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]

Hello Johannes,

I was trying to make use of the suggestion that you had posted on the website. Could make it work.

I would appreciate if you can take a look at my problem.

My Oracle SP signature i:

create or replace PROCEDURE PROC_BO_GET_PART_BASIC_INFO (p_type IN varchar2, p_name IN varchar2, p_revision IN varchar2, bo_cursor OUT sys_refcursor) IS

SP accepts 3 parameters.

SP returns a cursor (result set has around 10 cols and 1000s of rows).

What do I have to put in the 'Load Script' ? I tried all combination but did not work.

Thanks in advance

Neaz

[#3]

Hello Johannes,

I was trying to make use of the suggestion that you had posted on the website. Could make it work.

I would appreciate if you can take a look at my problem.

My Oracle SP signature i:

create or replace PROCEDURE PROC_BO_GET_PART_BASIC_INFO (p_type IN varchar2, p_name IN varchar2, p_revision IN varchar2, bo_cursor OUT sys_refcursor) IS

SP accepts 3 parameters.

SP returns a cursor (result set has around 10 cols and 1000s of rows).

What do I have to put in the 'Load Script' ? I tried all combination but did not work.

Thanks in advance

Neaz

[#4]

Hi, I am trying to do something similar but have no data returned. my stored procedure looks like:

CREATE PROCEDURE usp_v_test

@Currency varchar(3)

AS

select
@Currency as Currency

When running exec usp_v_test 'EUR' in SQL Management Studio, I get as expected a single line with a column "Currency" with the value 'EUR'.

In QV, I have the script SQL EXEC exec usp_v_test 'EUR' but nothing is returned. No field and therefore no data. Any clue?

Thanks!

[wadesims]

Patrick:

What you have there should work, possibly with a few tweaks. A few of observations/hints:

Make sure that you have a table name prior to the script. "EXEC exec" above should just be "EXEC". Here is what I used to test:

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SketchPad;Data Source=DT1234;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB12;Use Encryption for Data=False;Tag with column collation when possible=False] (mode is write);

Test:
SQL EXEC usp_v_test 'EUR';

Also make sure that you after you create your connect string using the "wizard" you manually change the righmost characters on your connect string from "];" to "] (mode is write);" as I have done in the example above.

Finally, make sure that under the Settings tab in the Script Priviledges section the box beside "Open Database in Read and Write mode" is checked.

I hope that this works for you!

- Wade

[#6]

Hi

I have a question thats sort of related. I have a Stored Procedure which requires variables passed in through Qlikview : this all runs fine.

My question is : is there a way to call a stored procedure from a button or some other method? I can run the procedure by reloading the report, but would prefer to simply call this procedure (it doesn't bring back any data so viewing the results is not needed).

[#7]

Wade

I have been facing the same issue. The work arounds will do for the time being. My concern is that it isn't always a consistent error. If I have the problem with one stored procedure, it is always a problem when I run the particular one. However, I do virtually do the same thing, selecting from a temp table at the end of a procedure, in several procedures, one procedure succeeds and the other fails.

What was Qliktech's explanation for this? Is this a problem in all versions or just the current release?

Thanks

-Brittany

[wadesims]

Brittany:

I think that my assumption that the problem stemmed from #temp tables may not have been entirely correct. The process also fails when using permanant tables if I have a procedure that has multiple steps. For example if I had a procedure that looked something like:

CREATE PROCEDURE select_from_perm_table AS

DELETE FROM dbo. perm_table;

INSERT INTO dbo. perm_table SELECT * FROM dbo. another_table;

SELECT * FROM dbo. perm_table;

then the above stored procedure would call return no rows when called.

The following stored procedure does return rows as expected

CREATE PROCEDURE simple_select_from_perm_table AS

SELECT * FROM dbo. perm_table;

GO

I'm curious if you are populating your #emp table in the same stored procedure as you are doing your select or if you are using a ##temp table that is already populated and your stored proceure that works is a simple select statement.

If you have some examples of what works and what fails, it might help speed up the analysis. This was at the bottom of our priority list so I have not hammered them about providing an explanation. I suspect that this is going to require a bit of 'looking under the hood' in order to determine what drives this behavior.

To your point, it is possible that the behavior changed with versions and I just missed it.

Thanks!

- Wade

GO

[#9]

Hello, Wade, Neaz, or anyone else using Oracle,

Has anyone actually been able to get an Oracle Stored procedure to return data to QV? The solutions above seem to work wonderfully for MS SQL, but I can't seem to get data from an Oracle SP no matter what I have tried, even if I simply create a stored procedure that returns SYSDATE. Any help would be greatly appreciated.

Thanks,

Richard in Atlanta

[#10]

I have had a lot of success using stored procedures for MS SQL 2000, 2008. I have been able to use #Temp tables and Permanent tables. I wonder if the folks who are having difficulty with data retunred from the stored procedure have a Select statement from the #Temp table before the table is dropped.

Create Table #Temp ( Name varchar(30), City varchar(15), State varchar(2))

Insert #Temp (Name, City)

Values('Smith, John', 'Philadelphia', 'PA')

Select * from #Temp

Drop Table #Temp

Issues that hasn't been mentioned:

• Multiple fields with the same field name in a select statement. This can be the case if you are using join clauses in your query. You have to give one of those field names an alias (City as Town).
• Aggregations, Case When Statements have to have an alias. Qlikview needs them to associate them in the datastructure of the QlikView App.

Hope this helps

Allen

[wadesims]

Allen:

I've run into problems with both #temporary and permanent tables when the select statement is the last statement in a series of SQL statements in a stored procedure. When the query completes, the connection is dropped along with any #temporary tables, so I have not been able to use a SELECT statement following the execution of a stored procedure when using #temporary tables and have had to resort to using a permanent table in that case.

I have only tested using SQL Server 2005 and OLEDB to connect.

- Wade