Problem with loading data from SQL stored procedure.

[#1]

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

[#2]

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.

[#3]

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 ?

[#4]

I have also this problem!

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

But does somebody knows the solution?

[#5]

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

[#6]

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.

[#7]

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?

[#8]

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.

[adhudson]

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

[#10]

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.