Running SQL commands in VBScript

Howdy,
Anyone know of some good online reference material for using vbscript to run commands against a SQL server? I can query just fine but if I want to backup a database I run into problems. Here is what I have so far...

If I try it without trying to dump the device, it says the device already exists. If I try and dump the device, it says it can't perform that action.

Code:

' Set the database connection information
sLogin = "tester"
sPwd = "tester"

' Create the ADO Connection and Recordset objects.
Set oCn = CreateObject( "ADODB.Connection" )
Set oRs = CreateObject( "ADODB.Recordset"  )

' Create a  FileSystemObject.
Set oFso = CreateObject("Scripting.FileSystemObject")

' Set the conneciton string, open the connection and get rows.
oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _
                       ";SERVER=" & sServer   & _
                       ";UID="    & sLogin  & _
                       ";PWD="    & sPwd    & _
                       ";DATABASE=" & MASTERDB

' Get DBid for the database
oCn.open
'oRs.Open "sp_dropdevice " &sDatabase
oRs.Open "sp_addumpdevice 'disk', '" &sDatabase &"', '" &sBakdir &"\" &sDatabase &"_data.bak'", oCn

' Read through all the rows.
Dim i
While Not oRs.EOF
  ' Get the column data for each column in the row.
  For i = 0 to oRs.Fields.Count - 1
    sDbid = sDbid & oRs.Fields(i).Value
    ' Separate the fields with commas
    If i < oRs.Fields.Count - 1  Then
    	sDBid = sDbid & ","
    End If
  Next
  oRS.MoveNext

Wend
oRs.Close
End Sub

 

sure!

http://www.asp101.com/articles/carvin/sqldmobackup/default.asp

 

Alrighty, well I got my little script working, but I am having a bit of a problem at the backup stage. For any large databases, VB gives me a timeout expired after about 30 seconds. I tried searching and found some posts about SQL procedures that make the server wait and such. I don't think that is the correct answer, seems to me there should be come command for extending the timeout. On the other hand, the detach function works fine... Any ideas? A snippet of the code is below, I figured it would go somewhere in my connection string.

Code:

Sub PublishDB()
	'Write to Log
	sLogMessage = "Reached PublishDB()" & vbTab & vbTab & vbTab & "-" & vbTab & "Starting connection strings"
	Call WriteLog(sLog, sLogMessage, oFSO)

	oCn.close
	oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _
                       ";SERVER=" & sServer   & _
                       ";UID="    & sLogin  & _
                       ";PWD="    & sPwd    & _

[B]<- Somewhere in here??[/B]

                       ";DATABASE=" & MASTERDB

	oCn.open
	'Run backup command via sql server
	IF sDBOption = "Backup" Then
		oRs.Open "BACKUP DATABASE " & sDatabase & " TO " & sDatabase & "_back" & " WITH INIT", oCn
	ELSE
		oRs.Open "sp_detach_db " &sDatabase, oCn
	End If
	'Write to Log
	sLogMessage = "Reached UserCheck()" & vbTab & vbTab & vbTab & "-" & vbTab & sDatabase & " backup/detach string sent"
	Call WriteLog(sLog, sLogMessage, oFSO)

	Call ZipDB()
End Sub

 

;connection timeout=1600

put that at the end of your connstring.

that's 1800 seconds or 30 minutes. I know it's overkill, but you get the point.

 

Hey, thanks for the reply.

I tried that before and still get the timeout. I never tried it at the end of the connection string but even that didnt seem to matter (I think its mad at me).

Here is what it looks like now, maybe I put it in the wrong spot -

Code:

Sub PublishDB()
	'Write to Log
	sLogMessage = "Reached PublishDB()" & vbTab & vbTab & vbTab & "-" & vbTab & "Starting connection strings"
	Call WriteLog(sLog, sLogMessage, oFSO)

	oCn.close
	oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _
                       ";SERVER=" & sServer   & _
                       ";UID="    & sLogin  & _
                       ";PWD="    & sPwd    & _
                       ";DATABASE=" & MASTERDB	& _
                       ";connection timeout=1600"
	oCn.open
	'Run backup command via sql server
	IF sDBOption = "Backup" Then
		oRs.Open "BACKUP DATABASE " & sDatabase & " TO " & sDatabase & "_back" & " WITH INIT", oCn
	ELSE
		oRs.Open "sp_detach_db " &sDatabase, oCn
	End If
	'Write to Log
	sLogMessage = "Reached UserCheck()" & vbTab & vbTab & vbTab & "-" & vbTab & sDatabase & " backup string sent"
	Call WriteLog(sLog, sLogMessage, oFSO)

	Call ZipDB()
End Sub

 

try something like
oCn.open
oCn.timeout=1600

or try adjusting the time out on your SQL server.

 

I looked through those oCn connections a dozen times and kept missing the connection and command timeouts. I added the "oCn.Connectiontimeout=1600" and still had the problem but as soon as I added the "oCn.Commandtimeout=1600" it started working. Thanks for your help, you definately got me on the right path.

 

Doh!! that's what I meant to put.. I had the other on my clipboard.. sorry about that; Brain-fart ya know