topic Re: Can not execute SQL SEVER Stored Procedure in Qv in QlikView

Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 07:34:42 GMT

Hi Everyone,

Anybody knows how to load data from stored procedure?

I need to load data from sql server Stored Precedure in Qlikview. But each time no data or table loaded without error message.

Connect to sql server OK. The script work fine in sql server client.

I wrote the script like this:

Below is the ending snapshot after finished reloading.

Re: Can not execute SQL SEVER Stored Procedure in Qv

sorrakis01 — Fri, 31 Jul 2015 07:50:52 GMT

Hi Duo,

Table:

SQL

EXEC dbo.Store_procedure_Name '120','A';

If doesn't work look this link:

https://community.qlik.com/message/693191#693191

Regards

Re: Can not execute SQL SEVER Stored Procedure in Qv

Anonymous — Fri, 31 Jul 2015 08:06:28 GMT

on SQL Server you need additionally "execute" rights. normally users only get read permissions.

check that please!

Re: Can not execute SQL SEVER Stored Procedure in Qv

markodonovan — Fri, 31 Jul 2015 08:33:16 GMT

Hi Duo,

Silly question.

Does the stored procedure return data when you run it from the management studio ?

Mark

techstuffybooks.com

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 08:37:09 GMT

Yes, there are number of rows.

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 08:47:04 GMT

Thank you.

You mean i need additional rights if i have to execute in qlikview even if i can execute it from sql server management studio already?

Actually, I can execute the script from sql server management studio without any warning or error.

Re: Can not execute SQL SEVER Stored Procedure in Qv

sorrakis01 — Fri, 31 Jul 2015 08:51:16 GMT

Hi,

Look at link in my post the comment of Normelio Junior.

Regards

Re: Can not execute SQL SEVER Stored Procedure in Qv

Anonymous — Fri, 31 Jul 2015 08:56:00 GMT

no when you can execute it from SQL Server you got the permissions and it should work as well from qlikview

I donot remember what we did when we tried to use a stored procedure (in our case we got no Advantage) but you may define in Settings "open database in read and write mode"

Re: Can not execute SQL SEVER Stored Procedure in Qv

markodonovan — Fri, 31 Jul 2015 09:01:20 GMT

Hi Duo,

Are there any errors\warnings in the sql server error log ?

Thanks

Mark

techstuffybooks.com

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 09:01:42 GMT

Yes i have check "open database in read and write mode" . Thank you.

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 09:02:51 GMT

No. everything is fine. Thank you.

Re: Can not execute SQL SEVER Stored Procedure in Qv

markodonovan — Fri, 31 Jul 2015 09:05:57 GMT

Is the 'Generate Log' option on in the Document properties ?

If so, can you check the log for any problems.

Thanks

Mark

techstuffybooks.com

Re: Can not execute SQL SEVER Stored Procedure in Qv

sorrakis01 — Fri, 31 Jul 2015 09:09:16 GMT

Hi Duo,

A simple question: Can you connect to the database and load a simple query, without problems?

Regards,

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 09:26:25 GMT

Yes, without any problem.

I'm trying Normelio Junior's solution as you recommended.

1. I have checked "Open database in Read and Write mode"

2. Add "Mode is write".

The difference between me and the example Normelio Junior posted is that i have no user name and password because i log on to server by Windows profile.

3. Correctly wrote the script calling the stored procedure.

But No data and table returned. I don't know why.

Re: Can not execute SQL SEVER Stored Procedure in Qv

Anonymous — Fri, 31 Jul 2015 09:31:44 GMT

can you check which user is connecting to SQL Server from qlivkiew?

can you try to open SQL Server with same user and see if you can execute stored procedure?

mostly qlikview is using a technical user while connecting to SQL Server is done by your Windows user

both Need same permissions, please check

Re: Can not execute SQL SEVER Stored Procedure in Qv

sorrakis01 — Fri, 31 Jul 2015 09:32:31 GMT

Hi,

then use the counsel of Mark. Generate the Log.

Another question: You can call this procedure from another software and it response ok?

Regards

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 09:34:45 GMT

Thank you. I looked the log file.

There is a parameter "ScriptWantsExe" which value is "false". May this caused the issue？

Re: Can not execute SQL SEVER Stored Procedure in Qv

markodonovan — Fri, 31 Jul 2015 09:38:34 GMT

Mine is false and works ok.

Must be something else.

Mark

Re: Can not execute SQL SEVER Stored Procedure in Qv

markodonovan — Fri, 31 Jul 2015 09:40:12 GMT

What does it say in the log when your call the stored procedure?

Mine is like this:

31/07/2015 10:37:17: 0076 OLEDB CONNECT*Provider*

31/07/2015 10:37:17: 0078 load *

31/07/2015 10:37:17: 0079 sql exec get_test_data

31/07/2015 10:37:17: 1 fields found: branch, 19 lines fetched

31/07/2015 10:37:17: 0082 exit script

31/07/2015 10:37:18: Execution finished.

Thanks

Mark

techstuffybooks.com

Re: Can not execute SQL SEVER Stored Procedure in Qv

Fri, 31 Jul 2015 09:42:23 GMT

I used my Windows Account and Password when logging on to sql server both from qlikview and sql server management studio.