Thursday, March 22, 2012

escape clause causes error in lookup modified sql statement

I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'

Is this is a bug? Any help will be greatly appreciated.


The escape clause shouldn't be affecting the parameter usage if the SQL works without it. The lookup doesn't parse the SQL, so I suspect the problem is with the provider. Essentially, the lookup asks the provider to prepare the command and then to derive parameter information. The provider is failing in one of the above steps (probably the prepare step). I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

No comments:

Post a Comment