topic Re: SQL Server stored procedure returns no rows in QlikView

SQL Server stored procedure returns no rows

mo_bhatti — Mon, 15 Oct 2012 11:20:26 GMT

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,

Re: SQL Server stored procedure returns no rows

chematos — Mon, 15 Oct 2012 17:34:29 GMT

Take a look on this thread

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

hope this helps

Re: SQL Server stored procedure returns no rows

mo_bhatti — Mon, 15 Oct 2012 18:22:09 GMT

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.

Re: SQL Server stored procedure returns no rows

erichshiino — Mon, 15 Oct 2012 19:51:32 GMT

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

Re: SQL Server stored procedure returns no rows

flipside — Tue, 16 Oct 2012 08:22:16 GMT

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

Re: SQL Server stored procedure returns no rows

rajni_batra — Fri, 02 Nov 2012 11:10:49 GMT

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);

Re: SQL Server stored procedure returns no rows

Fri, 02 Nov 2012 11:32:46 GMT

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

Re: SQL Server stored procedure returns no rows

Thu, 29 May 2014 04:28:38 GMT

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

Re: SQL Server stored procedure returns no rows

phcaptjim — Thu, 09 Apr 2015 02:24:02 GMT

Adam,

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

Re: SQL Server stored procedure returns no rows

gdelaossat1 — Thu, 16 Apr 2020 18:56:42 GMT

That worked foe me!