Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

exec stored procedure

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

1 Solution

Accepted Solutions
Not applicable
Author

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]') ;

View solution in original post

9 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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.

Not applicable
Author

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!

Not applicable
Author

Hi nlhudson

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

Thanks!!

Not applicable
Author

Hello

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

regards

erichshiino
Partner - Master
Partner - Master

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

Not applicable
Author

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

Not applicable
Author

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]') ;

Not applicable
Author

Add "SET NO COUNT ON"

Add "SET NOCOUNT ON"