ADO Multi-step OLE DB operation Exception

[ThunderForest]

Trying to run an ADO query, with five parameters and a stored procedure and get this exception:

Error: EOleException with message Multi-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I've looked at the Microsoft FAQ on this, and I can only guess the problem is my connection string:

Provider=SQLOLEDB.1;Password=xyxyxy-d;Persist Security Info=True;User ID=username;Initial Catalog=xyxyxySQL;Data Source=ZZZZDATA;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYPC;Use Encryption for Data=False;Tag with column collation when possible=False

I create these parameters in this manner:

q1.parameters.createParameter('EndDate', ftDateTime, pdInputOutput, 10, CurrDateEditPlus.Date);
q1.parameters.createParameter('salesrep', ftString, pdInputOutput, 8, SalesRepComboBox.GetComboValue(SalesRepComboBox.Text));
q1.parameters.createParameter('Employee_ID', ftString, pdInputOutput, 8, EmployeeComboBox.GetComboValue(EmployeeComboBox.Text));
q1.parameters.createParameter('program', ftString, pdInputOutput, 10, ProgramComboBox.GetComboValue(ProgramComboBox.Text));
q1.parameters.createParameter('OrderBy', ftInteger, pdInputOutput, 10, OrderByRadioGroup.ItemIndex);

The following is added to the adoquery.sql property, which works just fine in a TQuery:

xyz_reportproce :salesrep,rogram,:EndDate,:OrderBy,:Employee_ID

Any help would be appreciated. Thanks.

Getting answers before I'm asked.
Providing answers if I can.

 

[DjangMan]

I would try seeing what the resulting sql query looks like after the parameters have been processed. Then try running that through the enterprise manager. Or turn on the SQL trace in the enterprise manager and see what SQL is trying to do.

Regards,

Django

 

[ThunderForest]

I found that if I drop the parameters from the adoQuery.SQL property, ie, just leave the stored proc, it seems that I connect, but now I get a timeout expired exception.

Does TAdoQuery automatically carry the parameters along with the SQL property statement?

Getting answers before I'm asked.
Providing answers if I can.

 

[DjangMan]

I thnk you can if you make the data source of the StoredProc point to a data source attached to the Query - similar to a Master-Detail relationship would.

I would suggest running the ADO procedure directly - something like:

with ADOStoredProc do
begin
  ProcedureName := 'CopyCHART';
  Parameters.Refresh;  
  Parameters.ParamByName('@ORIG_CHART_CODE').Value := 'something';   
  Parameters.ParamByName('@NEW_CHART_CODE').Value := 'else'; 
  ExecProc;
end;

Regards,

Django

 

[ThunderForest]

I used TAdoDataSet and increased the connection timeout and the timeout went away. The proc completed but my result set was empty and shouldn't have been. I noticed you placed the @ sign in front of the param name in your code above. Is that why?

Getting answers before I'm asked.
Providing answers if I can.

 

[DjangMan]

My example comes from running a MSSQL stored procedure so all of my parameters are prefixed with '@'.

Cheers,

Django