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