Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mo_bhatti
Contributor II
Contributor II

SQL Server stored procedure returns no rows

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

1 Solution

Accepted Solutions
mo_bhatti
Contributor II
Contributor II
Author

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.

View solution in original post

9 Replies
chematos
Specialist II
Specialist II

Take a look on this thread

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

hope this helps

mo_bhatti
Contributor II
Contributor II
Author

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
Partner - Master
Partner - Master

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
Partner - Specialist II
Partner - Specialist II

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

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

Not applicable

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

Not applicable

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

Adam,

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

gdelaossat1
Contributor
Contributor

That worked foe me!