Load from MSSQL stored procedure with / without temporary table

[#1]

Hello Experts,

I experienced some strange behaviour when I tried to load data from a stored procedure (MSSQL 2008 R2). My original task is a bit different, but after some analysis I was able to reduce the question to the following:

In which way can the definition of a stored procedure influence the results in QlikView, while the SQL-results stay the same? What exactly does the temporary table change?

How to reproduce the behaviour...

Step 1: define the procedure

CREATE PROCEDURE [dbo].[SimpleExample]

as

select 1 as ID

Step 2: use the procedure in a qvw-load-script

SQL exec [dbo].[SimpleExample] ;

Step 3: check the result --> as expected

Step 4: modify the procedure in the following way

ALTER PROCEDURE [dbo].[SimpleExample]

as

declare @tmptab table(ID int)

insert into @tmptab(ID)

select 1 as ID

select ID from @tmptab

Step 5: reload the qvw

Step 6: check the result --> no data was found

The results for both versions are the same, when the procedure is called via SQL Management Studio.

According to SQL Profiler both versions are called in the exact same way.

Thank you very much in advance for explanations.

[Clever_Anjos · Accepted solution]

Try

SET NOCOUNT ON

inside your procedure

[Clever_Anjos]

Or:

CREATE TABLE #tmptab (ID int)

insert into #tmptab(ID)

select 1 as ID

select ID from #tmptab

DROP TABLE #tmptab

[#4]

Thank you very much. - The NOCOUNT does the trick...