SQL Server Agent running VB Script problem

Question

SQL Server Agent running VB Script problem

Gary Cobden

Ten Centuries

Points: 1269
More actions
August 2, 2010 at 3:47 am

#144387

We utilise SQLSafe Policies to undertake our SQL Backups, and we have a number of databases that need to be restored to other servers overnight.
I have a small VB Script program to do the restores - see below
Option Explicit
on error resume next
Dim fso
Dim oFolder
set fso = createobject("scripting.filesystemobject")
set ofolder=fso.getfolder("f:\SQLSafe_Backups")
datetoday = now()
yr = mid(datetoday,7,4)
mth = mid(datetoday,4,2)
dy = left(datetoday,2)
restore
sub restore
for each f in ofolder.files
if strcomp(left(f.name,36),"WAVY-FIN01_CodisMaster_Full_" & yr & mth & dy,1)=0 and strcomp(right(f.name,5),".safe",1)=0 then
f1 = "f:\SQLSafe_Backups\" & left(f.name,36) & mid(f.name,37,4) & ".safe"
end if
next
Set wshShell = WScript.CreateObject ("WSCript.shell")
wshshell.run """C:\Program Files\Idera\SQLsafe\SQLsafeCmd.exe""" & "Restore CodisMaster " & f1 & " -DisconnectUsers -Replace -Server " & """CHAR-XXXXXX""" & " -WindowsUsername abc\def" & " -EncryptedWindowsPassword " & """123gtrs345="""
set wshshell = nothing
set fso = nothing
set oFolder = nothing
end sub
Now I know I am not the worlds best vb script programmer, but this program executes fine when run interactively in a command window.
However, if I run it via a SQL Agent Job using either the VB Script option or a command line option using
cscript //B E:\backup\restore_script_codismaster.vbs
the job reports as executing correctly, but in fact does absolutely nothing.
Can anybody assist me here please
Many thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

cscript //B E:\backup\restore_script_codismaster.vbs

You may use the Operating System command while creating the job step and append an output file to the cscript command like below.

cscript //B E:\backup\restore_script_codismaster.vbs >> C:\csriptoutput.txt

The output file may lead you to a possible resolution !

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

suleman_khowaja Valued Member Points: 71 More actions · Answer 2

Hi Gary,

I am kinda stuck in the similar issue... and tried various things but no luck so far..

Were you able to resolve your isssue..

I am running DTS that has a VBscript to executes one exe and passes certain paramters as follows:

WSHShell.Run chr(34) & Apppath & chr(34) & " " & AppParama,7,True

If i run DTS manually it runs fine but when SQL Agent runs it it does nothing... same issue like urs..

any suggestion will be appreciated.

thanks in advance.

-Suleman