0

I am trying to run an ADO execute method with a SQL stored procedure that uses a date parameter to run. I made sure the data type of the parameter matched what it is defined as in the stored procedure (INT). The variable the stored procedure seems to have it formatted correctly as well (sp_StoredProcedureName 20151019). The connection to the SQL server just times out at the specified time of 720 seconds. Earlier in my program, I have another ADO execute method that doesn't seem to have any issues running. However, there is no parameter needed to run that procedure.The stored procedure seems to execute fine when I execute it in SQL server management studio. Is there a reason my other stored procedure isn't running?

This is the section that runs fine

 strSQL = "sp_BuildInvoiceDate"
    cnn2SQL.Execute(strSQL, lngRecordsAffected, ADODB.ExecuteOptionEnum.adExecuteNoRecords)
    WriteLog((lngRecordsAffected & " updates using '" & strSQL & "'"))

Here is the code that times out

strSQL = "sp_UpdateCreditHeader " & varTemp
        'ERROR: -2147217871 - Query timeout expired
        cnn2SQL.Execute(strSQL, lngRecordsAffected, ADODB.ExecuteOptionEnum.adExecuteNoRecords) 
 WriteLog((lngRecordsAffected & " updates using '" & strSQL & "'"))

varTemp is defined in a previous step as an integer just as it is defined on the server.

Ave_Dog08
  • 1
  • 2
  • Possible duplicate. This is probably related to the following issues: http://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio http://stackoverflow.com/questions/834124/ado-net-calling-t-sql-stored-procedure-causes-a-sqltimeoutexception/839055#839055 – Rick Liddle Oct 20 '15 at 18:29
  • you aren't passing the parameter correctly. see http://stackoverflow.com/questions/2557606/how-do-i-associate-parameters-to-command-objects-in-ado-with-vbscript – devlin carnate Oct 20 '15 at 18:34
  • This might sound a bit ignorant, but why use ADODB instead of ADO.NET. Really not trying to spark controversy here, just curious to know if there's a technical background, or other, to this choice. – Luc Morin Oct 20 '15 at 18:34

1 Answers1

0

It was actually just a matter of the order of the code. I placed the Stored Procedure at the bottom of my code and it worked...

Ave_Dog08
  • 1
  • 2