Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to use SQLDMO_SCRIPT_TYPE options in code Expand / Collapse
Author
Message
Posted Monday, April 26, 2010 9:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:27 AM
Points: 10, Visits: 360
Hi,

I have the following code that scripts out all agent jobs on a server. I want the generated script to include If not exists condition coz i either want to skip or overwrite existing jobs.

Dim ArgObj
Dim x
Dim ServerName
Set ArgObj=Wscript.Arguments
x=ArgObj.Item(0)
ServerName=Mid(x,3)
Wscript.Echo(ServerName)


Dim oSQLServer
Dim oStream
Set oSQlServer = CreateObject("SQLDMO.SQLServer")
Set oStream = CreateObject("ADODB.Stream")
oSQLServer.LoginSecure=True
oSQLServer.Connect ServerName

Dim idStep
Dim ScriptJob
Dim CountJobs
Dim JobName
Dim ScriptAllJobs

For Each oJob In oSQLServer.JobServer.Jobs

JobName = oJob.Name
if (InStr(UCase(JobName), "MIRRORING") > 0) Then
Wscript.Echo("Skipping Job"&JobName)
else
ScriptJob = Replace(oJob.Script(4
,"C:\"& JobName &".sql") , ServerName, "(local)")
ScriptAllJobs = ScriptAllJobs & ScriptJob

End If
Next

oStream.Open
oStream.WriteText (ScriptAllJobs)
oStream.SaveToFile ("C:\SQLAllScripts.sql"), 2

oStream.Close
oSQLServer.DisConnect

Set oStream = Nothing
Set oSQLServer = Nothing
Set ArgObj=Nothing

Where and how do i implement in this code?

Post #910463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse