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

[isaiah82]

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!

[#3]

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

[#4]

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