Stored Procedure

[#1]

Hi,

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

and read the returned table in qlikview

Thanks,

Mona

[hic]

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

[#3]

Thanks Henric.

But how about returning table not a single value?

Could you give me an example please?

[#4]

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', '%'

[hic]

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

[Bjorn_Wedbratt]

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', '%')};

[#7]

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?

[Bjorn_Wedbratt]

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?

[#9]

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