?
Solved

how to create job from .sql script?

Posted on 2008-06-19
3
Medium Priority
?
4,135 Views
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\dbserver_mydatabase_01.safe" -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).

Thanks
-- Script generated on 6/19/2008 9:44 AM
-- By: sa
-- Server: dbserver
 
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  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)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- 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  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'SQLSafe - All Selected DBs - Wednesday' 
    SELECT @JobID = NULL
  END 
 
BEGIN 
 
  -- 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\dbserver_mydatabase_01.safe" -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\dbserver_mydatabase2.safe" -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 
 
END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:

Open in new window

0
Comment
Question by:jam21000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Accepted Solution

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

Author Closing Comment

by:jam21000
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.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823401
You're welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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