SP not returning Data

[#1]

Hi All,

I have an issue where when i execute a store procedure, i do not get any data back, i'm also not seeing any error.

When i execute the SP in SSMS with the same account, i'm seeing that the SP works as intended. The strange part is also, that other SP's, present in the same DB and schema, are working as intended. Some of them only have a different SP name, the rest is the same.

Any thoughts are greatly appreciated.

KG,

Nick

[duffman1968]

Hey Nick,

This one drove me crazy. Thought I would share.

The SP might have multiple steps, which result in several counts, Qlik doesn't know what to do with every count it will see. A single SQL statement which only has one resulting count is fine. For those that produce more then one count use the NOCOUNT statement. You could put it in every SP if you want, just be aware you will get no counts.

In your SQL Server SP(s)

ALTER PROCEDURE [dbo]. [MyProc]

AS

SET NOCOUNT ON

your sql statement(s)

SET NOCOUNT OFF

END

GO

Hope this helps.

Regards,

Patrick

[#3 · Accepted solution]

hi Patrick,

Thank you for your input. While it did not directly solved the problem, it did point met in the correct direction to look.

What we have found, is that the issue lay in the temp tables. Adding "SET NOCOUNT ON" did not help in this case. turning off the temp table for testing did show that the SP works.

Changing from openquery() to CTE solved my issue in the end(the second link solved the issue).

KR

Nick

For any one else who is running into this issue, i have some links for you in order to start troubleshooting:

When is a Result Set not a Result Set?

This is in general a good one to start.

Loading Qlik Sense from a stored procedure with temp tables

The stored procedure were originally written for crystal reports and ssrs. I want to harness the existing logic in the stored procs that use temp tables. My problem is that for some reason those type of stored procs won't import any data. It is almost like they can't create the tables and are returning a null dataset with no errors. Realistically CTE and table variables are not the focus of the problem rather they were an example of a non-scalable solution.