Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]') ;
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.
BI Consultant
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.
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!
Hi nlhudson
I try with u tips, but not work, the same error appear
Thanks!!
Hello
The error for exec store, in my case is the same, but if i call a view of my mssql, work perfect
regards
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
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
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]') ;
Add "SET NO COUNT ON"
Add "SET NOCOUNT ON"