Skip to main content

I have a very simple stored procedure. When I use a variable in the dynamic sql I get no properties in the ServiceInstance but without the variable it works perfectly. Executing the sql directly in SSMS works fine.

 

So... this doesn't work (no properties created in the ServiceInstance):

ALTER Procedure  dbo]..ToolNumberDetailListForProduct] @ToolNumbers nvarchar(1024)ASDECLARE @sql nvarchar(1000)SET @sql = 'SELECT * FROM FlattenedProductToolNumber AS ToolNumbers WHERE ToolNumbers.Number IN '+ @ToolNumbersEXEC sp_executesql @sqlGO

This DOES work (properties are generated in the ServiceInstance) and the ONLY thing I changed is replacing @ToolNumbers with '(123456)' :

 

ALTER Procedure edbo].]ToolNumberDetailListForProduct] @ToolNumbers nvarchar(1024)ASDECLARE @sql nvarchar(1000)SET @sql = 'SELECT * FROM FlattenedProductToolNumber AS ToolNumbers WHERE ToolNumbers.Number IN '+ '(123456)'EXEC sp_executesql @sqlGO

Ány ideas? I'm tearing my hair out here.

 

TIA

In your SQL Server Service Instance, do you have "Native SQL" checked off? Try toggling that and refreshing the Service Instance to see if it then appears.

Have you had a look at this document? http://help.k2.com/displaycontent.aspx?id=7349

It states that "When the target objects are SProcs it executes them with the SET FMTONLY option set to ON. This returns only metadata to the client and is typically used to test the format of the response without actually executing the query."

It goes on to explain how to test your SP with SET FMTONLY to ensure that it is going to work correctly and what to check if it doesn't.

 

I hope this helps.

Thanks Eric, that was a helpful doc. Based on the screenshot attached the columns should be picked up by the Service Broker.


Thanks, yeah I did try that. Good suggestion but no luck.

Interesting. Yeah that certainly looks like a successful result. I am not sure what is going on there. It might be worth getting a support ticket logged to see if they have any other ideas. It is possible that you may be bumping to a bug although that does seem like a very straight forward stored proc that should just work.

Agreed, thanks Eric. I'll update with what K2 says.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings