Powershell script using SMO to script job creates file, but file fails when executed

Question

(SQL Server 2008 Enterprise x64, running under Windows Server 2008 R2 x64)

I have successfuly created a SQL Agent Job step using Powershell to run this code to script all jobs out to a file.  The file gets created just fine; I actually found that using a command shell step that pipes in a script file was necessary to avoid a malformed file. (here is the PowerShell Code):

Powershell Code:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" 10.1.1.1\SQLPROD
$srv.JobServer.Jobs | foreach {$_.Script()} | out-file \\SQLBackups\SQLPROD\failoverscripts\Create_SQL_JOBS.sql

However, when I go to my other server, open a new query window, and execute the Create_SQL_JOBS.sql script to have the jobs recreated, I get tons of errors saying that variables have already been declared - as a result no jobs are created.  (Not having GO in between each job creation section is probably the issue).  This is to be an automated process - manual intervention of adding GO is not an option for me.

Errors (there are many errors, this is as sample set of them:

Msg 134, Level 15, State 1, Line 95
The variable name '@ReturnCode' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 104
The variable name '@jobId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 243
The label 'QuitWithRollback' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 245

The label 'EndSave' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Line 249

I was expecting something more like how SSMS scripts out mulitple jobs and works just fine.

Can anyone suggest a better way? I am also open to using VBScript in a SQLAgent Step as well. A Nice to have would be something also filters the jobs to script with a condition (JobName Not Like 'AdminJob -%'

Thank you very much for helping me,

Erik

---

Erik A. Grob, MCITP, MCDBA

Answer 1

The batch terminator isn't included in SMO script unless you send the output to a file using SMO and set the necessary properties:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$a =  New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions

$a.filename = "C:\SQLBackups\SQLPROD\failoverscripts\Create_SQL_JOBS.sq
$a.AppendToFile = $true
$a.ToFileOnly = $true
$a.ScriptBatchTerminator = $true

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" 10.1.1.1\SQLPROD
$srv.JobServer.Jobs | foreach {$_.Script($a)}

Notice I've created a scripting options object called $a and pass it to the Script() method. You could also do something like this

$srv.JobServer.Jobs | foreach {$_.Script() -join ";`n"} | out-file \\SQLBackups\SQLPROD\failoverscripts\Create_SQL_JOBS.sql

 

 

Answer 2

Thank you, but I am still getting the same results; errors regarding variable declarations. Here is the code I have:

set-ExecutionPolicy Unrestricted

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$a = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions $a.filename = " \\172.18.48.43\SQLBackups\SQLPROD\failoverscripts\Create_SQL_JOBS.sql"

$a.AppendToFile = $true

$a.ToFileOnly = $true

$a.ScriptBatchTerminator = $true

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" PRDSQL03\SQLPROD

$srv.JobServer.Jobs | foreach {$_.Script($a)}

 

Thank you for the help again.

---

Erik A. Grob, MCITP, MCDBA