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]
SET NOCOUNT ON
your sql statement(s)
SET NOCOUNT OFF
Hope this helps.
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).
For any one else who is running into this issue, i have some links for you in order to start troubleshooting:
This is in general a good one to start.
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.