topic Stored Procedure in QlikView

Stored Procedure

Thu, 22 Mar 2012 13:16:12 GMT

Hi,

Please i want to know how to read SQL stored procedure in qlikview

and read the returned table in qlikview

Thanks,

Mona

Stored Procedure

hic — Thu, 22 Mar 2012 13:37:02 GMT

You must first create a CONNECT statement that has '(mode is write)' in the end.

Then you must, in the script editor, on the "Settings" tab at the bottom, check the control "Open databases in ... write mode".

Then you should use

SQL EXECUTE <ProcName>

in the QlikView Script. (Or if it is SQL EXEC <ProcName>? I think different drivers have slightly different syntaxes...)

/HIC

Stored Procedure

Thu, 22 Mar 2012 14:23:48 GMT

Thanks Henric.

But how about returning table not a single value?

Could you give me an example please?

Stored Procedure

Thu, 22 Mar 2012 14:30:03 GMT

when i tried your code

this error appear to me

OLEDB read failed

sql

Declare @bank_header_cd varchar(10),

@from_s_date varchar(20),

@to_s_date varchar(20),

@co_no varchar(10),

@curr_cd varchar(4)

EXEC [dbo].[ar_bank_balance_total_report_1] '14','01-01-2012','31-01-2012','c', '%'

Stored Procedure

hic — Thu, 22 Mar 2012 15:45:35 GMT

You will get the entire table back.

See more on other threads, e.g.

http://community.qlik.com/thread/31726

http://community.qlik.com/thread/3223

/HIC

Re: Stored Procedure

Bjorn_Wedbratt — Fri, 23 Mar 2012 08:25:42 GMT

Hi Mona,

If you're using OLEDB try to execute the SP using Call, instead of Execute, like:

{call SalesByCategory('Produce', '1995')}

See: http://msdn.microsoft.com/en-us/library/windows/desktop/ms677227(v=vs.85).aspx

So in your case it would be something like:

SQL

Declare @bank_header_cd varchar(10),

@from_s_date varchar(20),

@to_s_date varchar(20),

@co_no varchar(10),

@curr_cd varchar(4)

{Call [dbo].[ar_bank_balance_total_report_1]( '14','01-01-2012','31-01-2012','c', '%')};

Stored Procedure

Sun, 25 Mar 2012 09:24:50 GMT

Thanks Bjorn,

but when i tried to execute the stored proc. using call

the same error still recieved which is "OLEDB read failed"

What's the problem Please?

Stored Procedure

Bjorn_Wedbratt — Thu, 05 Apr 2012 10:00:27 GMT

Hi Monika,

Sorry about the delay in response.

I'm sorry to say that without further information and traces from the OLEDB provider, it's difficult to say what the root cause may be. It could be restrictions on the db server for example.

From QlikView perspective the only limitation is what Henric pointed out:

- QlikView must be allowed to open connections in read/write mode (check-box in Script editor)

- OLEDB connection must be opened in write mode (done with the parameter (mode is write) in the connect string)

Which version of MS SQL are you using and which version of MS OLEDB provider are you using?

Have you tried the provider in any other program, such as MS Excel?

Stored Procedure

Sat, 07 Apr 2012 19:05:35 GMT

Hi,

No need to declare parameters at qlikview end. You need to use Declare @para1 in sql server stored procedure side.

if you need to pass parameter to SP then use Let at qlikview script. ex

Let date = today().

Load A, B

sql exec (mystoredproc '$(date)')

Do a little check, use qlikview debug to see what statement is getting generated just before calling SP. Mind single quotes. Calling SP is not that big deal.

Regards,

Shubhasheesh