topic exec stored procedure in QlikView

exec stored procedure

Fri, 04 Nov 2011 19:00:20 GMT

Hello:

I try execute one stored procedure in my qlikview and ever recieve this error:

OLEDB read failed

sql

exec sp_pvrmvc

the script is

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;.......

sql
exec sp_mystored

Any idea?

Thanks in advance

Regards

César Estrada

exec stored procedure

Miguel_Angel_Baeyens — Fri, 04 Nov 2011 19:07:12 GMT

Hi César,

It seems that the driver has some problem to get to the server, and the syntax seems to be just fine. Does that work if you do a simple SELECT instead of the EXEC to retrieve any field from any table, or it stills shows the error? Is the user running the query with the integrated security granted permissions enough to run that stored procedure?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

exec stored procedure

Fri, 04 Nov 2011 19:15:27 GMT

The following are the tricks I found to get stored procedures to work:

1. Under the Settings tab on the Edit Script window, select "Open Databases in Read and Write mode"

2. Add "(Mode is write)" to the end of connection string. This should go right before the last semicolon and after the closing bracket.

3. Add "SET NO COUNT ON" to the top of your stored procedure code.

exec stored procedure

Fri, 04 Nov 2011 19:44:27 GMT

Hi Miguel:

1- Does that work if you do a simple SELECT instead of the EXEC to retrieve any field from any table, or it stills shows the error?

Yes work perfect

Is the user running the query with the integrated security granted permissions enough to run that stored procedure?

The user have datareader and datawrite in the base.

Thanks!

exec stored procedure

Fri, 04 Nov 2011 19:47:15 GMT

Hi nlhudson

I try with u tips, but not work, the same error appear

Thanks!!

exec stored procedure

Thu, 10 Nov 2011 14:23:31 GMT

Hello

The error for exec store, in my case is the same, but if i call a view of my mssql, work perfect

regards

exec stored procedure

erichshiino — Fri, 11 Nov 2011 17:27:06 GMT

HI, Cesar

Can you send us the message you receive in the log file

You can enable it on settings -> Document properties -> General tab -> check generate log file.

The log file will have the same name of the qvw but with the extension .log

Regards,

Erich

exec stored procedure

Fri, 11 Nov 2011 18:00:35 GMT

Hi Erich:

The Log is

11/11/2011 14:49:26: Execution started.

11/11/2011 14:49:26: QlikView Version:10.00.9061.7

11/11/2011 14:49:26: 0002 SET ThousandSep=','

11/11/2011 14:49:26: 0003 SET DecimalSep='.'

11/11/2011 14:49:26: 0004 SET MoneyThousandSep=','

11/11/2011 14:49:26: 0005 SET MoneyDecimalSep='.'

11/11/2011 14:49:26: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'

11/11/2011 14:49:26: 0007 SET TimeFormat='hh:mm:ss'

11/11/2011 14:49:26: 0008 SET DateFormat='MM/DD/YYYY'

11/11/2011 14:49:26: 0009 SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]'

11/11/2011 14:49:26: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

11/11/2011 14:49:26: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

11/11/2011 14:49:26: 0013 OLEDB CONNECT*Provider*

11/11/2011 14:49:27: 0015 sql

11/11/2011 14:49:27: 0016

11/11/2011 14:49:27: 0017 exec sp_procedure

11/11/2011 14:49:27: 3 fields found: account, name, date, Error: OLEDB read failed

11/11/2011 14:49:29: Execution finished.

The OLEDB is 64bits and the string is

OLEDB CONNECT TO [Provider=SQLNCLI10.1;

Integrated Security="";

Persist Security Info=False

;User ID=sa;

Initial Catalog=Emp;

Data Source=X00;

Use Procedure for Prepare=1;

Auto Translate=True;

Packet Size=4096;

Workstation ID=X01;

Initial File Name="";

Use Encryption for Data=False;

Tag with column collation when possible=False;

MARS Connection=False;

DataTypeCompatibility=0;

Trust Server Certificate=False];

Thanks, regards

César Estrada

exec stored procedure

Thu, 01 Mar 2012 14:28:48 GMT

The solution is:

Server XXX Instance AAA
Base Name : ZZZ
store procedure: gtstep1
Workstation ID : FFFF

OLEDB CONNECT TO [Provider=SQLNCLI10.1;Integrated Security="";
Persist Security Info=False;User ID=sa;
Initial Catalog=ZZZ;
Data Source=XXX\AAA;Use Procedure for Prepare=1;
Auto Translate=True;Packet Size=4096;Workstation ID=FFFF;
Initial File Name="";Use Encryption for Data=False;
Tag with column collation when possible=False;MARS Connection=False;
DataTypeCompatibility=0;Trust Server Certificate=False];
sql exec ('[XXX\AAA].[ZZZ].[dbo].[gtstep1]') ;

Re: exec stored procedure

Fri, 17 Jan 2014 06:05:12 GMT

Add "SET NO COUNT ON"

Add "SET NOCOUNT ON"