Hi,
I came across a request on MSDN where one of our colleague was running SQL 2008. He want to create a SQL Agent Job which when executed Script Out All existing SQL Agent Jobs. As per him he don't want to use SMO or VB.
I provided POWERSHELL script to do so.
1) Copy below code in a Text Pad and save it as JOBS.PS1
param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Jobs foreach {$_.Script()}
2) Create a New Job in SSMS.
3) In Steps add a step of Type as "Powershell".
4) In Command section provide location of file JOBS.PS1 and Server Name from where we want Jobs
Information.
C:\./JOBS.PS1 GURPSETH\SQL2K*
5) Go to Advances section for this Job Step and provide file in OUTPUT FILE section. idea is once this
command will run it should log the output in it.
6) Once done save this job.
7) Starts this job.
4 comments:
Hi Gursethi,
Thanks for your script. Unfortunately I got an error when I ran the job. Our SQL version is sql 2008 R2.
A job step received an error at line 2 in a PowerShell script. The corresponding line is ' System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'System.Reflection.Assembly]::LoadWithPartialName' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
The script works finally after I added [ at the start of [System.Reflection.Assembly]::
Only one thing I need to mention, you have to add sql service login into local admin groups, otherwise the script can't write output file. THe error message is "Executed as user: XXX. Unable to open Step output file. The step failed".
Thanks
When parsing or executing the results on another server. I get the following; looks like the job code redeclares the variable for each job.
Errors from executing the .sql file create from script
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
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 258
The variable name '@jobId' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 506
Incorrect syntax near 's'.
Msg 102, Level 15, State 1, Line 521
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 522
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 527
Incorrect syntax near 'DEL'.
Msg 102, Level 15, State 1, Line 529
Incorrect syntax near ',
@database_name=N'.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near 'PRDSQLCRM03'.
Msg 102, Level 15, State 1, Line 546
Incorrect syntax near '__RESTORE'.
Msg 319, Level 15, State 1, Line 546
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 561
Incorrect syntax near '',
@database_name=N'.
Msg 911, Level 16, State 1, Line 587
Database '?' does not exist. Make sure that the name is entered correctly.
Hi Guys,
First of all sorry because of some busyness I was not able to check your comments. Further Thanks to Fangtao for poining to mistake in my script.
Erik, I am able to run this job for other instance of SQL and its working fine. Not sure why exactly its failing at your end. Is it possible if you can send me screenshots at by address sethigurpreet@hotmail.com
Regards
Gurpreet Sethi
Post a Comment