Timeout Expired error calling sp from ADO script

DavidCookDavidCook Posts: 8 Bronze 2
edited June 23, 2007 9:56PM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi David,

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

    Here is an example.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • DavidCookDavidCook Posts: 8 Bronze 2
    Thanks guys, that was it. The scripts works now. Much obliged!
    · Share on Twitter
Sign In or Register to comment.