Scripting SQL Server Agent Jobs

Question

Scripting SQL Server Agent Jobs

hawg

SSCertifiable

Points: 6044
More actions
May 4, 2004 at 10:04 am

#161317

I know how to use Enterprise Manager to script SQL Server Agent jobs but I was wondering if anyone knows a way to create a script file from within a stored procedure.
I want to create a procedure that runs every night that scripts all jobs on my server and saves the output to a file on our backup server.
Any ideas?
Thanks,
Jason
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mpinnow SSChasing Mays Points: 612 More actions · Answer 1

I think I grabbed this script from Script Library here, but in any case, I run this little ActiveX script weekly on each server. I would hate having to recreate > 2000 jobs. Hope it helps you out.

Dim conServer

Dim fso

Dim iFile

Dim oJB

Dim strJob

Dim strFilename

Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")

conServer.LoginSecure = True

conServer.Connect "<servername>"

strFilename = "<path where output file will be placed>_JOBS.sql"

For Each oJB In conServer.JobServer.Jobs

strJob = strJob & "--------------------------------------------------" & vbCrLf

strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf

strJob = strJob & "--------------------------------------------------" & vbCrLf

strJob = strJob & oJB.Script() & vbCrLf

Set fso = CreateObject("Scripting.FileSystemObject")

Set iFile = fso.CreateTextFile(strFilename, True)

iFile.Write (strJob)

iFile.Close

Set fso = Nothing

hawg SSCertifiable Points: 6044 More actions · Answer 2

I'm going to try this but I have a question about running this. How do I set this up to run this ActiveX script? I've never worked with scripts like this before.

Do I save this script as some kind of file? If so, how do I run the file (script)?

I realize this is probably elementary, I've just never done it before.

Thanks.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

mpinnow SSChasing Mays Points: 612 More actions · Answer 3

Create a job in SQL Agent. For the step that is to run this script, choose "ActiveX Script" as the Type and then select the radio button "Visual Basic Script" as the language. Then you can schedule it to run as often as you like. This script will OVERWRITE any existing text in the output file, so if you want to track changes of your jobs, you will need to either modify the script or archive off copies of the output. Good luck.

hawg SSCertifiable Points: 6044 More actions · Answer 4

mpinnow,

I am using the script you gave me but I just found out something this morning that you may not be aware of.

When the script is created, the lines to add the job steps (search for sp_add_jobstep) are scripted in alphabetical order by @step_name and not @step_id. If the @step_id order is not in order (i.e. 1, 2, 3, ...), when you run the script you will get the following error:

Server: Msg 14266, Level 16, State 1, Procedure sp_verify_jobstep, Line 46

The specified '@step_id' is invalid (valid values are: 1..1).

If you put the job steps in order by @step_id, it works fine.

Since I like to name my job steps something that makes sense, I have employed a naming convention that will keep my job steps in order. Starting with job step #1, I put a letter and a dash before the descriptive name of each step. My job might have the following steps:

A - This is my job step 1

B - My job step 2

C - Step 3

D - Another job step

Even though this may be a pain if I have to change my job by adding a step in the middle, it will ensure that my scripts will run properly.

I just wanted to make you, and anyone else that wants to use this script, aware of this problem.

Other than this one problem, the script works great. I have even modified it to not only script each job individually but I also have it create one script with all jobs.

Thanks again,

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 5

This appears to be a post-SP3 issue. That is, what is your version of SQL Server? I am going to guess that if you run SELECT @@VERSION you will get a value greater than 8.00.760. That is, it would appear as though with SP3 and less that the job steps were ordered by jobid. Now they are ordered by jobname.

(NOTE: See my posting regarding post-SP3 issue with jobs with multiple job steps... I am just starting to investigate more though)

hawg SSCertifiable Points: 6044 More actions · Answer 6

You are correct, I am running SP3. I realized I didn't mention my version just before I got your response. (Oops!)

I will take a look at your post about SP3.

Thanks for clarifying this.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011