Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load from MSSQL stored procedure with / without temporary table

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.

1 Solution

Accepted Solutions
Employee
Employee

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

Try

SET NOCOUNT ON

inside your procedure

3 Replies
Employee
Employee

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

Try

SET NOCOUNT ON

inside your procedure

Employee
Employee

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

Or:

CREATE TABLE #tmptab (ID int)

insert into #tmptab(ID)

select 1 as ID

select ID from #tmptab

DROP TABLE #tmptab

Not applicable

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

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

Community Browser