topic SQL Stored Procedure into QlikView in QlikView

SQL Stored Procedure into QlikView

Tue, 18 Aug 2009 17:58:44 GMT

Hi,

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

Thanks in advance

Elzo

SQL Stored Procedure into QlikView

stephencredmond — Tue, 18 Aug 2009 18:10:31 GMT

Hi Elzo,

It is possible:

LOAD xx, yy;

SQL EXEC sp_xxyy;

But sometimes it is a better idea to go back a step and load the source data from the SP into QV and manipulate it there - you might get more and better results that way.

Stephen

SQL Stored Procedure into QlikView

Wed, 19 Aug 2009 12:29:44 GMT

hi elzo....

its possible we working it now will mail u qvw sample in couple of hrs

SQL Stored Procedure into QlikView

Anonymous — Wed, 19 Aug 2009 13:31:51 GMT

Hi,

Yes, SQL Server
By using the EXECUTE command in an SQL statement it's possible to execute stored procedures, if the user is granted access to do so on the server. Here are two examples:

Executing sp with input / output parameters

SQL
declare @var3 int
execute sp_myProcedure 5,3, @var3 output
Select @var3 as result;

Executing sp returning a result set

SQL Execute CustOrderHist 'CustomerNo';

ORACLE:

In Oracle, you can call a stored function with a syntax like:

var result number
exec :result := pkg_ims_status.ext_checkdbstatus()

The function will return a value to be stored in the variable result.

You can call the same function in QlikView using an ordinary SQL SELECT-statement like:

SQL SELECT
pkg_ims_status.ext_checkdbstatus() as status;

Running a stored procedure without returning any recordset:

Use the function CALL to specify the name of the stored procedure.

Example (Launches XYZ with the two parameters 2 & 5):

sql call XYZ ( 2 , 5 );

This works for both ODBC and OLE DB. However, when opening the connection using OLE DB, make sure to add (mode is write) before the finishing semi colon in the connection string.

Note1:
You need to make sure that you activated "Open Databases in Read and Write mode" in QlikView. This is done in the script editor on the tab "Settings".

Note2:
If using OLEDB as a provider, you will also need to add the parameter "mode is write" in the OLEDB-connection:
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Northwind;Data Source=mySQL;](mode is write);

SQL Stored Procedure into QlikView

Wed, 19 Aug 2009 13:42:00 GMT

Thank you so much. You're great!!!

SQL Stored Procedure into QlikView

Fri, 20 Nov 2009 18:08:45 GMT

Thanks a lot. It worked for me.

SQL Stored Procedure into QlikView

shumailh — Fri, 20 Nov 2009 18:25:10 GMT

Thanks Johannes for such a detail examples.

Regards,
Shumail Hussain

SQL Stored Procedure into QlikView

Wed, 20 Jan 2010 19:54:15 GMT

Hi,

I´m trying to read out parameters from a Oracle Stored Procedures.

Can you help me?

Thanks.

Federico Martino

SQL Stored Procedure into QlikView

Fri, 05 Feb 2010 10:42:15 GMT

Hi Archana..

Can you send us a sample document for stored procedure that returns table ?

? can't do it

Thanks..

SQL Stored Procedure into QlikView

Mon, 08 Feb 2010 09:51:17 GMT

hi Canolmez,
try this code snippet for any sp which takes two "dates" as input parameter and 1 outparameter. or u can create a new sample sp too if you are comfortable in writing stored procedures.
hopes this works for you
regs
archana
*************************************************************************************************************************

Input:

load

Input ('Enter an from date value', 'Input box') as value1_date,

Input('Enter an end date value', 'Input box') as value2_date

autogenerate 1;

Let FromDate = peek('value1_date',0,'Input');

Let DateTo = peek('value2_date',0,'Input');

sql

DECLARE @date1 datetime,

@date2 datetime,

@vol int

EXEC [dbo].[test_proc] '$(FromDate)', '$(DateTo)', @vol OUTPUT

SELECT @vol as vol

SQL Stored Procedure into QlikView

Tue, 02 Mar 2010 13:37:39 GMT

It works for SQL, thanks..

SQL Stored Procedure into QlikView

wadesims — Thu, 11 Mar 2010 20:31:12 GMT

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

SQL Stored Procedure into QlikView

wadesims — Thu, 11 Mar 2010 21:45:50 GMT

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

SQL Stored Procedure into QlikView

wadesims — Fri, 12 Mar 2010 17:36:45 GMT

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.

SQL Stored Procedure into QlikView

Wed, 14 Apr 2010 08:49:10 GMT

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

SQL Stored Procedure into QlikView

wadesims — Thu, 15 Apr 2010 14:42:36 GMT

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

SQL Stored Procedure into QlikView

Mon, 26 Apr 2010 13:39:54 GMT

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.

SQL Stored Procedure into QlikView

Wed, 26 May 2010 15:36:10 GMT

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

SQL Stored Procedure into QlikView

wadesims — Wed, 26 May 2010 16:58:11 GMT

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

SQL Stored Procedure into QlikView

Wed, 26 May 2010 18:16:23 GMT

Thanks for the reply Wade ... appreciate it !