SQL Server stored procedure returns no rows

[mo_bhatti]

Hi,

I have a stored procedure on a SQL Server 2008 instance which does not return any rows in QlikView.

The stored procedure works as expected and returns rows when executed within SQL Server Management Studio (SSMS).

The stored procedure has SET NOCOUNT ON specified at the very beginning.

The format of the connection string used is:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE_NAME;Data Source=SERVER_NAME.DOMAIN.COM,1433;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False];

The method of calling the stored procedure is:

CLOUD_DATABASE_USAGE:

SQL

EXEC dbo.udsp_Stored_Procedure '$(vParam)';

I have also tried adding (mode is write); at the end of the connection string along with enabling the option 'Open Databases in Read and Write mode'. Still no joy.  Interestingly, I can execute different stored procedures against an instance of SQL Server 2005 and get rows returned, so my current line of thinking is that it is something to do with the version of SQL Server.

What do I need to do to get the stored procedure to execute and return rows?  I have been struggling to find a resolution for a couple of days now so any guidance or support will be greatly appreciated.

Many thanks,

Mo

[chematos]

Take a look on this thread

http://community.qlik.com/message/261520#261520

hope this helps

[mo_bhatti · Accepted solution]

Figured out the issue. The stored procedure that was being invoked belonged to a schema other than dbo. 

Glad that its  resolved but I am a bit disappointed that QlikView does not raise a meaningful error message. Something along the lines of 'stored procedure does not exist/object not found' would have been a great clue, rather than simply proceeding as if the stored procedure had been invoked successfully but not returning any rows.

As a sidenote is this something that has been addressed in a recent service release/service pack?  If not, how do I go about raising a request for this enhancement to be added.

[erichshiino]

Hi,

I believe you can contact QlikTech support or your QlikTech parter, but I'm not sure it will be considered a bug.

If this cannot be considered a bug, you can request an enhancement as an idea here:

http://community.qlik.com/ideas

Regards,

Erich

[flipside]

Hi,

Have you tried setting ErrorMode to zero and viewing the error details after the OLEDB command, something like ...

Set ErrorMode = 0;

OLEDEB CONNECT TO {connection string};

CLOUD_DATABASE_USAGE:

SQL

EXEC dbo.udsp_Stored_Procedure '$(vParam)';

LET y = Num(ScriptError) & ': ' & ScriptError & chr(10) & ScriptErrorDetails;

set errormode = 1;

flipside

[rajni_batra]

heloo an u help me this:

created connection loaded table from SQL but need to revert back the status to DB after some changes,

How can i do this???

ODBC CONNECT TO SQL (XUserId is GAXEKXFMWLYAHYVOTLZEXEB, XPassword is LGGXNXFMWLYAHYVOTLZEXBC);

SQL

EXEC ReconDB.dbo.tblBookingComponentDetail ??????????

//

//A:

//SQL SELECT

//    "Booking ID",

//    "Component Type",

//    "Cost Price ",

//    ID,

//    "Selling Price ",

//    status

//FROM ReconDB.dbo.tblBookingComponentDetail;

//STORE * from A into recon.qvd;

Directory;

LOAD [Booking ID],

     [Component Type],

     [Cost Price ],

     if([Cost Price ]>30000 and [Cost Price ]<40000,'A',

               if([Cost Price ]>40000 and [Cost Price ] < 50000,'B','C')) as status,

     ID,

     [Selling Price ]

     FROM

recon.qvd

(qvd);

[#7]

Hi Rajni,

Please see the following link.

Wirting back to SQL can be achieved using macro.

http://community.qlik.com/message/201408#201408

Regards,

Prasath

[#8]

I've hit this is as well.

Just worked out how to fix it.

My stored proc has temp tables, so I had to include the following at the very start of the SQL stored proc:

SET NOCOUNT ON

That fixed it for me.

Adam

[phcaptjim]

Adam,

Thanks for your post.  Adding that qualification to my load script corrected my issue.

[gdelaossat1]

That worked foe me!