How can we help you today? How can we help you today?

Timeout Expired error calling sp from ADO script

I'm experiencing a "Timeout expired" error condition running the SQLBackup 5.1 stored procedure from within an ADO VBScript on a large-ish (40+Gb) SQL Server 2000 database. The script works fine if I run it on a 1 Gb database, only the large one causes the timeout error condition. The interesting point is that the SQLBackup 5.1 GUI shows the backup as successful, even when the ADO script has eventually died with the timeout error.

Note that running the commandline version of SQLBackup 5.1 on the large database works fine, it's only the stored procedure version that is causing me grief.

Here is the script I am using:


' VBS script to backup up an SQLServer 2000 DB to a file share using Red-Gate's SQLBackup's stored procedure via ADO
' 20070618 dac

Option Explicit
Dim myConn
Dim mySQLCmdText

' Initialise connection object
set myConn=CreateObject("ADODB.Connection")
' Open connection to SQL server
myConn.Open="Provider=SQLOLEDB;Data Source=DB-SERVER;Initial Catalog=master;Trusted_Connection=Yes;"
' Load command to run stored procedure
mySQLCmdText= "exec master..sqlbackup N'-SQL ""BACKUP DATABASE [BigDB] TO DISK = [\\DB-SERVER\SQLBackup\BigDB-20070622.sqb] WITH INIT, COMPRESSION = 1""'"
' Execute the command
myConn.Execute mySQLCmdText
' Close connection
myConn.close
' That's All Folks!


Any ideas?


Cheers

David Cook
Technical Services Manager
Latrobe Health Services
David Cook
0

Comments

3 comments

Sort by
  • Brian Donahue
    Hi David,

    You want to set the SqlCommand's CommandTimeout property to 0.

    Here is an example.
    Brian Donahue
    0
  • petey
    If the backup completed and you experienced a "Timeout expired" condition, it may be because of the ADO CommandTimeout value. Did it timeout after 30 seconds, because that appears to be the default value? Try setting it to 0 to wait until the backup completes, or a larger value that should be more than adequate for the backup to complete e.g.
    myConn.CommandTimeout = 900  '15 minutes
    petey
    0
  • DavidCook
    Thanks guys, that was it. The scripts works now. Much obliged!
    DavidCook
    0

Add comment

Please sign in to leave a comment.