Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

wdchristensen
Contributor III

Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

I am trying to pull data with the following script in Qlik Sense 3.2:

LIB CONNECT TO 'ABCSQLServer';

temp01:

NoConcatenate

LOAD *;

SQL EXEC MyDB.dbo.usp_GetDataFromSQL;

Store temp01 into [lib://SomeQlikLocation\QVDFolder\GetDataFromSQL.QVD] (qvd);

drop table temp01;

This code works fine as long as the stored procedure pulls data without using a temp table (CTE, Table Variable). However, as soon as I include a #tempTable in the stored procedure everything fails. It appears as if the connection doesn’t have access to tempdb but it does. Does anyone know why this is happening? Your help is greatly appreciated.

1 Solution

Accepted Solutions
gareth_wilson12
Contributor II

Re: Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

I've solved this before by putting NOCOUNT ON after the BEGIN of my SP;

BEGIN

SET NOCOUNT ON;

could you try that?

May also be worth turning ansi warnings off.

4 Replies
gareth_wilson12
Contributor II

Re: Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

I've solved this before by putting NOCOUNT ON after the BEGIN of my SP;

BEGIN

SET NOCOUNT ON;

could you try that?

May also be worth turning ansi warnings off.

wdchristensen
Contributor III

Re: Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

/* This code worked after implementing recommendations of Gareth Wilson */


USE [Master]

GO


SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


-- =============================================

-- Author: Will

-- exec [dbo].[Test_TmpTblforQlik]

-- =============================================

CREATE PROCEDURE [dbo].[usp_GetDataFromSQL]


AS

BEGIN

SET NOCOUNT ON;


DROP TABLE IF EXISTS #MyTest_01


SELECT *

INTO #MyTest_01

From

(

Select 'A' as MyLetter, 1 as MyNumber

Union All

Select 'B' as MyLetter, 2 as MyNumber

Union All

Select 'C' as MyLetter, 3 as MyNumber

Union All

Select 'D' as MyLetter, 4 as MyNumber

) as Qry_01


SELECT *

FROM #MyTest_01


END


GO

wdchristensen
Contributor III

Re: Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

Previously I had been rewriting my stored procedures with CTEs but this time was different since I used several windowing functions (row_count) and the performance boost of temp table was really needed. Thanks for your quick response and the spot on solution! I really appreciate the help.

Out of curiosity, do you know why the nocount must be turned on?

gareth_wilson12
Contributor II

Re: Importing Data from SQL Server 2016 from Stored Procedure w #TmpTbl

No Problem William,

I'm not 100% sure about why this works, I can only speculate that Qlik cannot receive the Done_In_Proc messages when the SP executes.

Thanks

Gareth

Community Browser