Loading Qlik Sense from a stored procedure with temp tables

[wdchristensen]

I am trying to pull data from a stored procedure SQL Server 2016. Everything works great until I try to pull from a stored procedure that uses temp tables. I initially thought that it was a permissions issue with the service account connecting to SQL Server but apparently there are no special permissions required to create or drop temp tables.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c903a88b-1a5b-4ba7-ab3d-81156e0dec72/permis...

Additionally, I connected to the database in SSMS using the service account and was able to run the stored procedure with no issues. I suspect I could rewrite the code to use table variables on CTEs on the SQL side but this would be a huge pain since I have to pull from dozens of existing store procs with temp tables. Any help would be greatly appreciated!

LIB CONNECT TO 'DB0123_ABC';

MyTest:

LOAD

*;

SQL EXEC Apps_Custom_DHP.dbo.usp_MySimpleSPTest

[Anil_Babu_Samineni]

Even you are pulling data for dozens of SP's. Qlik has power to capture those and gets the records. Here, My question is how you want to store into variable when CTEs for SQL

[wdchristensen]

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.