maintenance job step help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

maintenance job step help

Author

Topic

doctortt
Starting Member

8 Posts

Posted - 2007-08-17 : 16:57:12

Hi guys. I set up a maintenance job on my SQL 2000 server to back up a database. Before the job kicks in to back up the database, I would like to set up a step to check for a file first on another box. If the file exists, continues; otherwise, terminate the job. When I create a step, how can I check whether a file from \\testserver\temp\flag.log is there? What kind of method I can use?

I tried to paste some vbscript codes in the step; however, I don't know how to use vbscript to execute the next step. Below is the vbscript codes - please see bolds.

Option Explicit
On Error Resume Next
Dim objFso, LogFile, dteStart, sFolder, WshShell
sFolder = "\\botest3\backup\flag.log"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set WshShell = WScript.CreateObject("WScript.Shell")
set LogFile = objfso.getfile(sFolder)

dteStart = Now()

Do Until DateDiff("n", dteStart, Now()) > 5 OR Not objFSO.FileExists(sFolder)
Wscript.Sleep 10000
Loop

If objFSO.FileExists(sFolder) Then
<don't know what to put here to execute the next step>
Else
WshShell.logevent 1, "Time Expired - Cannot Find Flag.log"
<also don't know what to put here to stop the job>
End if
Wscript.Quit
Set objFso = Nothing
Set sFolder = Nothing
Set dteStart = Nothing
Set dteStart = Nothing
Set WshShell = Nothing

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 18:54:54

Why are you using Scripting for this?
You can use an ordinary SP and attach a scheduled job for this.

E 12°55'05.25"
N 56°04'39.16"

doctortt
Starting Member

8 Posts

Posted - 2007-08-20 : 10:44:51

I'm using script for this because I don't know anything about SQL, but I'm assigned to this job.
Can someone please assist and write a simple SP for me?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-20 : 21:37:47

You need to call sql statements even in the script. Take look at 'create procedure' in books online.

doctortt
Starting Member

8 Posts

Posted - 2007-08-21 : 12:54:56

Thanks for your suggestion. I spoke with my dba and manager, and we couldn't use 'create procedure' due to some political/technical reasons. Is there any return code in VB for On Success Action: Goto the next step AND On failure action: quit the job reporting failure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources