topic Re: Stored Procedure in QV is Not Working in QlikView

Stored Procedure in QV is Not Working

lylererger — Sat, 21 May 2016 10:25:58 GMT

Hi friends!

Please help me to understand why in MS Management Studio stored procedure "Exec dbo.QlickView_GetDGlistNewSession CALL Connection(1);@SessionID = null, @dtLastUpdateFrom =null, @dtLastUpdateTill =null;" successfully returns 48514 rows, but in QlikView it doesn't, i.e. returns nothing.

Example of my QlikView Extractor below:

CALL ProjectFolders(1)
CALL Connection(1);
LET vNow = Timestamp(Timestamp#(Now(1)),'DD.MM.YYYY hh_mm');

tmpData:
LOAD *;
SQL
Exec dbo.QlickView_GetDGlistNewSession @SessionID = null, @dtLastUpdateFrom =null, @dtLastUpdateTill =null;

IF NoOfRows('tmpData')>0 THEN    
     TRACE [-----Successfully done-----];
     LET vtmpFile = '$(vINCREMENTALData)\DGlistNewSession_$(vNow).qvd';
     EXECUTE cmd.exe /C del /Q $(vtmpFile);
     STORE tmpData INTO  [$(vtmpFile)] (qvd);
ENDIF

CALL DropTables('tmpData');
DISCONNECT;

Version of QV 11.20 SR 10.

Maybe I'm doing something wrong? Any ideas ?

In attachment result of that procedure in MS Management Studio.

Thanks in advance.

Re: Stored Procedure in QV is Not Working

petter — Sat, 21 May 2016 11:20:10 GMT

How does your result set looks like in my SQL Server Management Studio. The name of the columns and a sample of the first few rows?

Re: Stored Procedure in QV is Not Working

lylererger — Sat, 21 May 2016 11:31:37 GMT

Petter, the customers IT department says that i can use this stored procedure without any parameters.

Example of result in attachment.

What could be the problem is? Maybe this is another bug?

Re: Stored Procedure in QV is Not Working

petter — Sat, 21 May 2016 11:47:32 GMT

It doesn't seem like the column names should create any problem. But from your previous screenshot it seems like the Stored Procedure returns multiple result sets. Invoking a stored procedure from the load script with the SQL statement will only retrieve the first result set and disregard the following result sets.

You would probably want the last result set - right?

Re: Stored Procedure in QV is Not Working

lylererger — Sat, 21 May 2016 12:00:51 GMT

I need to get two columns data "DG_CODE" and "QVCT_LastUpdate", 48514 rows.

Re: Stored Procedure in QV is Not Working

Clever_Anjos — Sat, 21 May 2016 12:39:46 GMT

Did you try this?

tmpData:

LOAD *;

SQL

Exec dbo.QlickView_GetDGlistNewSession

Re: Stored Procedure in QV is Not Working

petter — Sat, 21 May 2016 12:45:52 GMT

Then you probably have to get IT to change or create a new stored procedure that delivers only an single result set.

Re: Stored Procedure in QV is Not Working

lylererger — Sat, 21 May 2016 12:52:15 GMT

Anjos thanks, but I already tried all possible options, including the option proposed by you. It doesn't helps.

Petter Thank you very much for your prompt feedback. Will do so.

Re: Stored Procedure in QV is Not Working

Clever_Anjos — Sat, 21 May 2016 12:58:44 GMT

One very important thing

You procedure must have a SET NOCOUNT ON

Or you should include it into your QlikView

tmpData:
LOAD *;
SQL
SET NOCOUNT ON
Exec dbo.QlickView_GetDGlistNewSession

Re: Stored Procedure in QV is Not Working

lylererger — Sat, 21 May 2016 13:06:37 GMT

Omg. Anjos thanks a lot. It works

What this line means ?