SQL Stored Procedure into QlikView

[#1]

Hi,

i'm currently trying to get data with stored procedures into Qlikview.
Is this possible?

Thanks in advance

Elzo

[#2]

It works for SQL, thanks..

[wadesims]

Could you expand a bit more on the "(mode is write)" that appears in the CONNECT statement? I'm having difficulty determining if this is part of the SQLOLEDB string or if this is additional information that is required by QlikView to establish a connection.

If this is not part of the SQLOLEDB string, where can we find documentation about the syntax for the CONNECT statement?

I can get a simple stored procedure to execute and return rows, but longer running stored procedures seem to fail with an authentication eror message.

Thanks! - Wade

[wadesims]

Here is a more specific example of a problem for which I am searching for documentation:

I am having trouble combining the string (mode is write) with the string (XPassword is ABCDEFGHIjkLMnOPQRSTuVw) in the CONNECT statement. It interprets the second string as garbage.

I have temporarily worked around the problem by setting the Settings under User Preference on the Security tab by checking under the section "Always Override Security" the box labeled "Script (Allow Database Write and Execute Statements)"

[wadesims]

Post script - determined an acceptable syntax through trial and errror:

In this particular case, adding "(XPassword is ABCDEFGHIjkLMnOPQRSTuVw, mode is write)" to the connect string works when you need to both use a specific user name and password and also open the database in read and write mode.

[#6]

Hi,

My Stored Procedure does not return data to qlikview, when I use sql temp tables or table variables in my stored procedure.

Actually it is working in sql server management studio but in qlikview there is a problem with those tables. But I have to use them.

Do you know anything to fix it??

[wadesims]

Tuba:

I have run into the same or similar problem. I am running SQL Server 2005 as a database. For me, I can use #temp tables in a stored procedure just fine, but when I try to select rows from a temp table as the final step in the process, it behaves as though the table has been dropped before the rows are returned. As a work-around I have left all of my #temp tables that are used as intermediate steps in the procedure in place, but have created a permanent table in which to store the rows corresponding to the result of the query. I run a separate "SQL SELECT" statement immediately following the execution of the stored procedure to return the desired rows.

It isn't an optimal solution, but it seems to work and gets the job done.

I hope that this helps!

- Wade

[#8]

Thanks for your reply,

I am running SQL Server 2008 and the same problem exists. I also have created a permanent table to store the result.

It is not a good solution but I think there is no other way to handle it.

[#9]

I am getting an OLEDB error

OLEDB read failed

SQL EXEC sp_xyz

I think this might be something to do with the connection mode, My stored procedure is also writing data in tmp tables the database and i have tried adding "mode is write" at the end of the connection string but it still giving me the same error !

my connection string is

CONNECT TO [ConnectMIS;DSN=ConnectMIS;UID=usr-a;PWD=usr-a;DATABASE=mis] (UserId is "mis-a", Password is "mis-a",mode is write);

I am able to execute the stored procs which are simple reads with no temp tables

Anyone knws how to sort this out

[wadesims]

Amit:

We have been experiencing the same problem. I do not think it is something you are doing.

As a work-around, we are creating permanent tables and then running three steps in our script:

1) Execute the stored procedure that populates the permanent table in SQL Server.

2) SQL SELECT * FROM <permanent table> into a resident QlikView table.

3) Execute a stored procedure that truncates the permanent table in SQL Server.

We had some reps from QlikTech in the office yesterday and they are checking into the reason why we get this behavior, but it appears to be common to all users. If anyone gets this to work, please follow up with a post!

- Wade

[#11]

Thanks for the reply Wade ... appreciate it !