Stored Procedure in QV is Not Working

[lylererger]

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.

[petter]

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?

[lylererger]

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?

[petter]

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?

[lylererger]

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

[Clever_Anjos]

Did you try this?

tmpData:  
LOAD *;  
SQL 
Exec dbo.QlickView_GetDGlistNewSession

[petter]

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

[lylererger]

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.

[Clever_Anjos · Accepted solution]

One very important thing

You procedure must have a SET NOCOUNT ON

Or you should include it into your QlikView

1. tmpData:   
2. LOAD *;   
3. SQL  
4. SET NOCOUNT ON
   
5. Exec dbo.QlickView_GetDGlistNewSession 

[lylererger]

Omg. Anjos thanks a lot. It works

What this line means ?