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]

this is an example of how I created my connectionstring and how I called the stored proc.

In the screenshot you can see the open database in Read and Write mode is ticked.

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=Username;Initial Catalog=database;Data Source=dbserver;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MAV-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is encryptedPassword, mode is write);

SQL exec [prLoadingBayScheduleRpt] @Loadingbay = '741,770,771'

[wadesims]

JKnoesen:

I wanted to follow up with a thank-you for your earlier post about including "SET NOCOUNT ON;" in the opening lines of the stored procedure. For those of us who are new to SQL Server (I come from an Oracle background) and who failed to see the note that Microsoft includes in the default template that says "SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements" your comments were as helpful as actually reading the directions. That is good advice for stored procedures in general and should become standard practice if it is not already.

It turns out that my suggestion for using an ODBC connection in lieu of an OLEDB connection can cause problems. It worked fine and would reload in the client version, even on the production server, until I tried to run the job from the QlikView Enterprise Management Console and then the report would fail because the console didn't have visibility to the ODBC connection. Your solution does work and does allow the process to be scheduled. Your input allowed me to solve a problem with a job that failed when I put it into production yesteday afternoon but which had to work this morning. Keep up the good posts!

- Wade

[#4]

Thank you very much!

[#5]

One thing to note. If you are including the password in the OLEDB statement the syntax is:

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Northwind;Data Source=mySQL;](XPassword is DFJSKLdkSDFS mode is write);

[#6]

Hi All,

Seeing that this issue was mention about 6 months ago, I was wondering if anyone knows whether a solution has been given by Qliktech without having to use the workaround by creating a permanent table for your Stored Procedure.

I am still receiving error; OLEDB Read Failed when calling a stored proc.

[wadesims]

Wynand:

There may have been more than one thread on this subject, but if you are using SQL Server, make sure that your stored procedure includes "SET NOCOUNT ON;" after then BEGIN statement. From the default template provided by Microsoft SQL Server Management Studio:

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

I fought this for a long time until I added that to my stored procedure.

- Wade

[SunilChauhan]

Dear archana,

is it possible to provide this input after reload of application.

because of user of my application will not reload the application.

but user want to see the data on different start and end dates.

if u have any idea please let me know.

its very urgent.

[#9]

Thank you so much Johannes Sundén .

[#10]

Experienced this issue when calling a multi-stage SQL stored procedure.

Simply adding set "nocount on" and "set nocount off" at either end of the stored proc fixed it

No need to set mode to write or change access permissions (important as was bridging inter-unit reports and would have delayed process by months to get permission for write access!)

[#11]

Hi

I have multiple stored procedures called one after the other in a qv report.

I want to know how does qlikview reloads the script? It executes say for example exec proc_1, exec proc_2, exec proc_3 all at a time or at a time it executes only one (the one which is called earlier) exec proc_1??

Thanks