Mapping UDF Parameters to Variables

As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.

So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.

The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.

Dick,

This doesn't help you, but I think there IS a bug with the OLEDB data source and passing variables. I have exactly the same issue with a simple query to a Microsoft FoxPro data source (which I've brought to this forum before and got no solution).

I went with the package variable solution - it's not as elegant but it works

Do you know where we should post bug reports for SQL 2005?

Rich

|||

Thanks Rich,

It's some consolation the hear from someone else about the problem. Foxpro is a "foriegn" application (albiet a Microsoft one). It's the fact that the problem occurs within SQL Server 2005 that is a bit of a surprise. Add to this the fact that Reporting Services seems to handle UDF parameters correctly (indicating that there is no reason why it shouldn't work) and it is a bit frustrating.

As you say contructing the entire SQL Command Text as a Package Variable is a work around but this is not really what was intended, Mapping Package Variables to parameter markers is more within the spirit of SSIS and much nicer. It seems to work with Stored Procedures for example.

My company has links with MS so I will try to find out on Monday how to officially report this.

Best regards,

Dick Campbell

Manipulating parameters passed into a report - errors with parameters

Hi,
I've discovered a problem with repserv sp1 that I just can't see a way
around at the moment.
Basically we have a custom front end that allows the user to select
the params from either dropdown controls or textboxes. The text boxes
allow four states:
1/ All records
2/ Exact match
3/ Partial Match
4/ Starts with
The partial match is causing me the problem. Basically in the code
behind i'm prepending/appending the like clause character % to the
contents of the textbox i.e.
user enters 0123 into the textbox, the param passed into the report is
%0123%
Thats where the problem lies, repserv doesnt seem to accept that as a
valid param. It doesnt give any errors but it doesnt display the
required resultset. In the params returned to the user on the report,
it shows
23% has been passed in. The same thing happens if I type the url in
manually i.e.
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
NameRefValue is declared as a string param, NameRefType is declared as
an integer param
Anyone have any ideas on this? Is it possible to manipulate the params
passed into the report before the engine actually processes them? If
this is possible I could just pass in the string without the %
characters & based on NamRefType value, add the % in the report itself
before the dataset is returned.
Cheers
SiSi wrote:
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
>
Just read this the other day:
"NOTE Wildcards in Your SQL The filters you specify for your reports
are based on Visual Basic .Net 2003 syntax, so you'll find that the
pattern matching with the Like operator uses * as a wildcard and not %,
as you might use in SQL Like expressions."
_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
hth
--Mike|||Thanks Mike,
I've just tried it and although the strange corruption of the
parameter is now ok, I still get incorrect results returned.
I use the % syntax in the starting with section with no problems, it
just seems to fail on integers and partial matches
Cheers anyway, it gave me a couple of ideas there!
Si
On Mon, 14 Feb 2005 07:54:53 -0600, "Mike Donnellan"
<spamspamspamandspam@.AT@.donnellanDOTcom> wrote:
>Just read this the other day:
>"NOTE Wildcards in Your SQL The filters you specify for your reports
>are based on Visual Basic .Net 2003 syntax, so you'll find that the
>pattern matching with the Like operator uses * as a wildcard and not %,
>as you might use in SQL Like expressions."
>_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
>Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
>hth
>--Mike|||Percent character is used to URL encode/escape other characters, so it
should itself also be encoded.
Check if this url works:
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:mn4111pcl3qv3p6803i6pbstmhpch4lcge@.4ax.com...
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
> Thats where the problem lies, repserv doesnt seem to accept that as a
> valid param. It doesnt give any errors but it doesnt display the
> required resultset. In the params returned to the user on the report,
> it shows
> 23% has been passed in. The same thing happens if I type the url in
> manually i.e.
> http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
> NameRefValue is declared as a string param, NameRefType is declared as
> an integer param
> Anyone have any ideas on this? Is it possible to manipulate the params
> passed into the report before the engine actually processes them? If
> this is possible I could just pass in the string without the %
> characters & based on NamRefType value, add the % in the report itself
> before the dataset is returned.
> Cheers
> Si|||Thanks Lev,
Yes that does seem to work ok. IS there a specific way I should be
encoding these characters or simply replace % with %25 ?
Cheers
Si
On Mon, 14 Feb 2005 21:16:54 -0800, "Lev Semenets [MSFT]"
<levs@.microsoft.com> wrote:
>Percent character is used to URL encode/escape other characters, so it
>should itself also be encoded.
>Check if this url works:
>http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3|||As a rule, I always try to limit query-string parameter values to the
actual value, and do formatting elsewhere.
Instead of passing the value %0123% via the query-string, why don't you
just pass the 0123 and add the "%" characters via an expression within
the RDL. This should completely avoid the problem you encountered with
encoding.
~Lance
http://weblogs.asp.net/lhunt/|||Thanks Lance,
Thats what I originally wanted to do but was unsure as to how to
manipulate the parameters before passing through to the stored proc.
In the end i've passed it all through as you say but direct to the
stored proc for processing & it now works fine.
I'd still be interested to know how to do it the way you mention.
Regards
Si
On 15 Feb 2005 07:02:36 -0800, "Lance" <lancehunt@.gmail.com> wrote:
>As a rule, I always try to limit query-string parameter values to the
>actual value, and do formatting elsewhere.
>Instead of passing the value %0123% via the query-string, why don't you
>just pass the 0123 and add the "%" characters via an expression within
>the RDL. This should completely avoid the problem you encountered with
>encoding.
>~Lance
>http://weblogs.asp.net/lhunt/|||I would normally recommend using the exact solution you chose, except I
wasnt sure if you were using a StoredProcedure or SQL. You definitely
should stick with your current implementation.
However, there are some cases where you can't use Stored Procedures,
such as with many ODBC connections to legacy systems. In such cases, I
recommend dynamically building your value for the LIKE expression in
the RDL.
Here are the basic steps:
1. Open "Data" tab from designer.
2. Select your DataSet from dropdown
3. Click on the "..." to go to properties.
4. Click on the Parameters tab
5. Locate the parameter in question
6. Modify the parameter value (right hand column) to use an expression.
7. Use the expression:
"="%" & Parameters!MyParam.Value & "%"
Instead of the default expression
"=Parameters!MyParam.Value"
8. Close Properties and you're ready to go!
Enjoy!
Lance Hunt
http://weblogs.asp.net/lhunt/