how to create job from .sql script?

Posted on 2008-06-19
Last Modified: 2010-04-21
I have a sql job (which uses Idera SQLSafe to backup our databases) with 60 steps.  Each step is a CmdExec command like the following example:

"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "Audit" "\\SQLSafe_server1\h$\SQLSafe\Wednesday\" -server "dbserver" -backupdescription "mydatabase" -backupname "mydatabase" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt

I need to change the 'Wedensday' to 'Thursday' (and other days as well) and though I can go into each step and do it one by one, this is entirely too time consuming.

I generated a sql-script from the job in question, of which I attached as a code snippet.

The question is this.... How do I create a job from that sql-script?

If I create a new job and try to 'Open' that as a command entered as a step within that job, I get an error advising me that there are 50220 characters which is too large.  Also, even I could do it this way, I wouldn't be creating the job the way I wanted to originally, which is a job with many CmdExec steps (and which is the way the job is originally created).

-- Script generated on 6/19/2008 9:44 AM
-- By: sa
-- Server: dbserver
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'SQLSafe - All Selected DBs - Wednesday')       
  IF (@JobID IS NOT NULL)    
  -- Check if the job is a multi-server job  
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''SQLSafe - All Selected DBs - Wednesday'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'SQLSafe - All Selected DBs - Wednesday' 
  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'SQLSafe - All Selected DBs - Wednesday', @owner_login_name = N'sa', @description = N'SQLsafe Backup', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'SQLsafeCmd: Backup mydatabase', @command = N'"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "mydatabase" "\\SQLSafe_server\h$\SQLSafe\Wednesday\" -server "dbserver" -backupdescription "mydatabase" -backupname "mydatabase" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- another 58 steps run between the above and the below steps (EDITED for the sake of this example) --
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 60, @step_name = N'SQLsafeCmd: Backup mydatabase2', @command = N'"C:\Program Files\Idera\SQLsafe\SQLsafeCmd" Backup "mydatabase2" "\\SQLSafe_server\h$\SQLSafe\Wednesday\" -server "dbserver" -backupdescription "mydatabase2" -backupname "mydatabase2" -init -compressionlevel "ispeed" -managementserver "SQLSafe_server" -threads "3" -noprompt ', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 1
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Weekly Wednesday', @enabled = 1, @freq_type = 8, @active_start_date = 20070102, @active_start_time = 223000, @freq_interval = 8, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
GOTO   EndSave              

Open in new window

Question by:jam21000
  • 2
LVL 60

Accepted Solution

chapmandew earned 500 total points
ID: 21822975
Your script creates the job itself, so run it in a new query editor window.

Author Closing Comment

ID: 31468821
Ha!  I thought so and looking at the comments the sql-script wrote (which I looked at/realized just now, stupid me), I should have seen the confirmation there already.

I just modified the script and ran it in query analyzer and it created the job.

Thanks for the quick response/confirmation.  much appreciated.
LVL 60

Expert Comment

ID: 21823401
You're welcome.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question