Parameters in Function in OLE DB Source

Hi there!

I need to use some parameters in an OLE DB Source like that:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)

The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:

"Falsche Syntax in der N?he von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.

What is the reason for that? It there any solution or a workaround?

Thanks, Torsten

The OLE DB source may be having problems detecting the parameter in your query. I have the impression that it just understands simple queries. The work around is to place the query in a variable, Set the 'EvaluateAsExpression' property to TRUE, and then use an expre ssion to make the query dynamic.

Then in the OLE DB source, choose the option that says your query is in a variable and select the variable from the dropdown list. This is a very common practice to pass the query into source components

Here you can find more posts on how to do that:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=query+variable+expression&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Thanks!

I've found another way: I execute these functions in an "Execute SQL-Task" an put the results into a variable, what i use in the OLE DB Source. Works fine:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= ?
and dbo.fkt_get_guelt_bis (posh_valid_to) >= ?