topic Problem with loading data from SQL stored procedure. in QlikView

Problem with loading data from SQL stored procedure.

Tue, 29 Sep 2009 16:40:50 GMT

Hello,

I have an SQL stored procedure sp_assetslist

When I run this procedure in SQL Query Analyzer (exec sp_assetslist) I get few records with columns(fields):

id, fk_assettypes_id, fk_users_id, rawdata

Now I'm trying to show these data in QlikView. I tried to use solution code which I found on the QlikTech Forum but is does not work.

My code is:


CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CCE_Assets;Data Source=PLWASQL01;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PLBHZ4V2J;Use Encryption for Data=False;Tag with column collation when possible=False;];
LOAD *;
SQL EXECUTE sp_assetslist;

I also tried with:


LOAD id, fk_assettypes_id, fk_users_id, rawdata;
SQL EXECUTE sp_assetslist;

but I get an error message: Field not found - <id>

Any idea how to manage it?
Thanks in advance - Brgds
Mariusz

Problem with loading data from SQL stored procedure.

Tue, 29 Sep 2009 20:25:10 GMT

Try using upper case (capital) letters for the field name. Use ID not id

My SQL queries always generate the field names in upper case unless I alias the names to a lower case or mixed case name. The database software doesn't care but QlikView is case-sensitive.

Problem with loading data from SQL stored procedure.

Thu, 18 Mar 2010 19:17:23 GMT

I have the exact same problem..... and it is not solved by making the returned variable names in Caps.

Basically i am trying to get a multiple rows for multiple records through a SP and feed that into a qlikview report.

Any advise ?

Problem with loading data from SQL stored procedure.

Mon, 03 May 2010 10:46:56 GMT

I have also this problem!

I think it is a problem because we have a OLEDB connection.

But does somebody knows the solution?

Problem with loading data from SQL stored procedure.

Tue, 21 Sep 2010 02:00:48 GMT

thanks a lot. if i use odbc connection, it is ok. but how could i use ole db to solve this problem?

Problem with loading data from SQL stored procedure.

Tue, 12 Apr 2011 22:38:55 GMT

I'm getting this problem too, so it looks like it hasn't been solved in the past 11 months. I have a ticket open, if they come up with something I'll post back here. Reading data from a sproc is a very basic database operation, I really don't understand why it's so difficult to implement properly.

Problem with loading data from SQL stored procedure.

Tue, 10 Apr 2012 14:39:02 GMT

A little late, but for what it's worth: this is apparently a known issue, QlikView defect 37807 (they must have reset the count a few times). Has anyone seen this behavoir in QV11, or seen previously-problematic stored procedures run OK on v11?

Problem with loading data from SQL stored procedure.

Tue, 10 Apr 2012 15:05:39 GMT

Oh yeah, the fix! This problem disappeared when I replaced my OLEDB connection string with ODBC. Presumably QlikView will come around to mid-90s technologies soon.

Also, there seems to be a correlation in that sprocs which use temp tables have this problem, but sproc without are OK on OLEDB.

Problem with loading data from SQL stored procedure.

adhudson — Wed, 11 Apr 2012 04:05:56 GMT

Hi,

Why dont you try to use the native SQL command for executing the stored procedures?

LOAD id, fk_assettypes_id, fk_users_id, rawdata;

SQL EXEC sp_assetslist;

I have used this several times and I got no error.

Regards

Andrew Hudson

Problem with loading data from SQL stored procedure.

Wed, 11 Apr 2012 14:33:48 GMT

That's exactly what has not worked for me - when the stored procedure used temp tables, and I connected via OLEDB to an MS SQL 2K8 database.

Re: Problem with loading data from SQL stored procedure.

isaiah82 — Thu, 12 Jul 2012 15:05:08 GMT

I have been dealing with this same issue > SQL Svr 2008, and using temp tables. Works fine over ODBC, but nothing is returned over OLEDB. I was able to get around this by adding set nocount on to the proc, e.g.:

BEGIN

SET NOCOUNT ON

.......

And it works like a champ!

Re: Problem with loading data from SQL stored procedure.

Thu, 09 Aug 2012 17:21:14 GMT

Thank you for your post this worked for SQL Svr 2005 as well!

Re: Problem with loading data from SQL stored procedure.

Wed, 26 Dec 2012 06:34:49 GMT

Hi Marius,

If you are using Microsoft SQL Server as the database, then please perform below steps:

Database : OLE DB -> Connect -> Select Microsoft OLEDB Provider for SQL Sever / SQL Native Client -> and in the Connection Tab -> Enter your server details, Database details and click Test Connection.

If the result is success then click OK.

Now try to execute the query by: sql exec sp_assetslist;

If there are any parameters to the sproc : sql exec sp_assetslist $(Param1), $(Param2), ... ;

Hope this might help.

Thanks,

Sai Krishna