SQL Agent Jobs

Jun 29, 2012 at 12:59 AM

I love this tool.  I am in the middle of a 20 server hardware migration with 38 instances.  This tool has been invaluable in scripting out and configuring new server.

One thing that is driving me crazy is the SQL Agent Job output file cannot be run to completion.  I have not taken the time to fix it manually.  I wrote a powershell to extract the jobs to individual files and then push with RedGate Multi-Script.  The current tool appears to have issues with declaring variables for each sql agent job.

Also, why can you not capture the Linked Server password if you can capture the Login password?  That would be a great feature add also.

Thought I would make that suggestion and see what happens.  

Keep up the great work.

Jul 9, 2012 at 10:45 PM

What error do you get when you script jobs?  I'm not aware of any problems.  Can you post the actual error you received?

The challenge with scripting the linked server password is that there isn't any way to recreate the linked server login using the already encrypted password.  The CREATE LOGIN statement allows that.  The command to add a login for a linked server doesn't.  It's a limitation in SQL Server.

Jul 9, 2012 at 11:12 PM

Thanks for the response Bill.  Below are the errors I when I run SQL Server Agent Jobs.sql file with more than one agent job extracted.  Not sure if a GO or ; needs to be added.  I have not had time to dig in and take a look.

 

Msg 134, Level 15, State 1, Line 53The 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 62The 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 99The 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 101The 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 104The 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 113The 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 164The 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 166The 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 169The 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 178The 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 229The 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 231The label 'EndSave' has already been declared. Label names must be unique within a query batch or stored procedure.

Jul 10, 2012 at 10:21 PM

I've exchanged a few emails and wanted to update this thread with the result.  The challenge is that the application will generate a script to match the version of the SQL Server instance it is scripting.  So if you're scripting out a SQL Server 2005 instance it will generate a script to run against SQL Server 2005.  If you try to run that script against a different version (like SQL Server 2008) you may encounter the errors above.  May scripts will work.  Jobs are one of the areas that seems to cause the most problems.

If you want to use this script to migrate to a newer instance, you'll need to hard code the target version. Just look where TargetServerVersion property is set and hard code the version you want to script for.  Note that you'll have to run this on a machine with that version of SMO installed but you can point it at the older version of SQL Server.