Load from MSSQL stored procedure with / without temporary table

Fri, 05 Jun 2015 12:41:42 GMT

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]

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]

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.

Re: Load from MSSQL stored procedure with / without temporary table

Clever_Anjos — Fri, 05 Jun 2015 13:01:20 GMT

Try

SET NOCOUNT ON

inside your procedure

Re: Load from MSSQL stored procedure with / without temporary table

Clever_Anjos — Fri, 05 Jun 2015 13:02:34 GMT

Or:

CREATE TABLE #tmptab (ID int)

insert into #tmptab(ID)

select 1 as ID

select ID from #tmptab

DROP TABLE #tmptab

Re: Load from MSSQL stored procedure with / without temporary table

Fri, 05 Jun 2015 13:24:51 GMT

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

topic Re: Load from MSSQL stored procedure with / without temporary table in QlikView

Load from MSSQL stored procedure with / without temporary table

Re: Load from MSSQL stored procedure with / without temporary table

Re: Load from MSSQL stored procedure with / without temporary table

Re: Load from MSSQL stored procedure with / without temporary table