Problem retrieving data from a Store Procedure

[Anonymous]

Hi,

I'm trying to retrieved data from a Store Procedure in Qlikview. When I execute my qvw in the QlikView's console I see the connection to the base and then... nothing happend. It's like the SP don't retrieved any record but when I execute the SP in the SQL Server's Client I get data.

This is my load script:

LOAD *;

SQL EXEC T1000.TOA.[dbo].[SP_AGENDA_DISP_VS_QUOTA_TOA];

And this is my connection string:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=indicadores_dw;Data Source=T1000;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PC025-PC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is KQdXESdOUbMaM);

I got access to the SP's code, when I read it I saw the funtion openrowset as the only one that can give me a problem (I supposed).

This is SP's pseudocode:

SELECT * INTO #PEND

FROM openrowset('sqloledb','BlaBlaBla';'BlaBlaBla';'BlaBlaBla','exec TRAZ_PEDIDOS.DBO.GET_PEND')

SELECT bla,

               bla,

               bla

INTO #INFORME

FROM (SELECT * FROM openrowset('sqloledb','BlaBlaBla';'BlaBlaBla';'BlaBlaBla','select * from TRAZ_PEDIDOS.dbo.QUOTA_BLABLA ') Q

INNER JOIN

( SELECT bla,

                bla,

                bla

....

The other part of the code its just left joins, right joins and others ...

Any one has faced on to this problem?  Any idea? I'm trying hard to solve this problem but I don't have succeded.

Best Regards,

Leandro.

[Clever_Anjos · Accepted solution]

Please check if your procedure has a

SET NOCOUNT ON

[Anonymous]

The procedure hasn't a SET NOCOUNT ON

[Clever_Anjos]

I think that´s the problem

[Clever_Anjos]

Some other threads with same problem

'Set NoCount ON' in Stored Procedure

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

[Anonymous]

It works!

And I didn't need to modify the SP, my load script looks like this:

LOAD *;

SQL SET NOCOUNT ON; SQL EXEC T1000.TOA.[dbo].[SP_AGENDA_DISP_VS_QUOTA_TOA];

[Clever_Anjos]

Good