Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
lylererger
Creator II
Creator II

Stored Procedure in QV is Not Working

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.

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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 

View solution in original post

9 Replies
petter
Partner
Partner

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
Creator II
Creator II
Author

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

Example of result in attachment.Untitled.png

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

petter
Partner
Partner

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
Creator II
Creator II
Author

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

Clever_Anjos
Employee
Employee

Did you try this?

tmpData: 

LOAD *; 

SQL

Exec dbo.QlickView_GetDGlistNewSession

petter
Partner
Partner

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

lylererger
Creator II
Creator II
Author

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
Employee
Employee

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 

View solution in original post

lylererger
Creator II
Creator II
Author

Omg. Anjos thanks a lot. It works

What this line means ?