how to use SQLDMO_SCRIPT_TYPE options in code

• coolchaitu

  SSChampion

  Points: 10332

  More actions

  April 26, 2010 at 9:12 am

  #97275

  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?