multiple-step ole db operation generated errors

i wrote a script awhile back to gather some metrics for tracking ad objects to softgrid clients.  i kept getting some very strange execution behaviors each time i ran it, generating the following error:

Retrieving AD computer objects newer than: 12/6/2009 10:32:55 AM
myscript.vbs(149, 2) Microsoft OLE DB Provider for SQL Server: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

 

when looking closely at this problem, it turned out that this execution error was happening at the call for retrieving a sms report.  this is the block of code where it was failing.  i noted in red the line where the execution bombs out.

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordSet = CreateObject("ADODB.Recordset")


oConnection.Open("Provider=SQLOLEDB;Data Source=myServer;Trusted_Connection=Yes;Initial Catalog=SMS_XYZ;")
oRecordSet.Open "webreport_approle.wrspSMS00179",oConnection, 3,3

oRecordSet.MoveFirst
While Not oRecordSet.EOF
    iPatchMissing = oRecordSet(0)
    iPatchInstalled = oRecordSet(1)
    iPatchSaturation = oRecordSet(2)
    oRecordSet.MoveNext
Wend
 
oRecordset.Close
oConnection.Close

 

this shouldn’t be a problem, but as it turns out, this particular report queries against add/remove programs.  as such, the table can be crazy large which means queries can be crazy slow, especially for wildcard searches with like operators.  though, as long as you don’t go above the default timeout, you’ll probably never encounter this error.

the default timeout of the ado connection object is 15 seconds.  i wish i had known this before.  all that’s required is one small update (a single line) to this script and the execution occurs properly.  here’s the revised code snippet.

…
oConnection.Open("Provider=SQLOLEDB;Data Source=myServer;Trusted_Connection=Yes;Initial Catalog=SMS_XYZ;")
oConnection.CommandTimeout = 120
oRecordSet.Open "webreport_approle.wrspSMS00179",oConnection, 3,3
…

 

and now we’re good!

 

additional information:

• ConnectionTimeout Property (ADO)