Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Don't you just love riddles like that? I do and since I'm writing this post I get to pique your interest anyway I choose.
Perhaps it won't surprise you to know that long before becoming a Qlik Dork I was an SQL Dork. SQL Server this and SQL Server that I've done it. Including writing some very low level data drivers so that I could bypass the SQL Native drivers that are shipped with the product so that the application I was writing would have ridiculously fast performance. Anyway as part of that process I discovered years ago that a Result Set isn't always returned even though you can see the results when you run the command in a SQL query tool.
Recently a colleague asked for help when he couldn't get a stored procedure to run and low and behold my old friend the missing the Result Set showed it's ugly head.
Here is the scenario you execute a simple stored procedure and you can clearly see that as expected it returns the results.
So you create a tiny fragment of code in your Qlik application following tips you've read via the Qlik Community like so:
AdmissionTypes:
SQL exec spQlik_ListAdmissionTypes;
You do a Reload and voila the system reports no errors at all, yet no data is returned:
That doesn't make any sense does it. You can clearly see the results in your SQL Query tool, one of your closest personal friends from the Qlik Community swears Qlik can retrieve results from Stored Procedures and yet ... here it is no errors and yet no data. How can that be?
Well ... the truth is that SQL Server is most likely returning multiple result sets and that was the case for my colleague. You see many times in Stored Procedures developers utilize #Temporary Tables or do things that invoke multiple commands. If you are using the Microsoft SSMS you can press a tab that you may have seen for years and never pressed that is labeled "Messages" and it will show you output from executing your stored procedure that you don't see when you are looking at the Results that are returned. In this case you can see that there were actually two commands that were executed both of which returned data. The symptoms you see inside of QlikView or QlikSense is that only the first Result Set which in this case was 12 rows being inserted into a #Temporary Table. Thus a count of 12 for a completed command, yet No results.
One of the things that I learned the hard way many years ago is that the SSMS studio (don't worry what it was called years ago) iterates through ALL of the result sets and thus returns the data to you from each. Which is great for it, but how would Qlik be able to return the data from completely different commands to a single "table" object? It couldn't. So instead it returns the data from just the first returned result set. In this case ... an empty result. No errors. Yet no data either. Confounding I know.
Fret not my friend for there is a way around this conundrum. It comes in the form of a TSQL parameter/variable called "NOCOUNT." By simply including "set nocount on" prior to the actual code inside the stored procedure you instruct SQL Server to do the work, but keep the silly counts regarding how many rows were affected to itself.
That way instead of the system reporting all of the intermediate steps it simply returns a single Result Set which includes the final output data.
This way Qlik can actually return the Results that you expect it to return and your blood pressure will come back to normal.
Disclaimer - Helping you understand why you may not be seeing results in Qlik from executing stored procedures is in no way a suggestion that you should modify the code from vendors. This post is merely intended to help you understand why you may not be seeing the results that you expect to see.
