InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:38:06 GMT20http://www.sqlservercentral.com/Forums/Topic404712-462-1.aspxI fixed the problem part....Just joined with another table....Please review it...It is ok now... FROM msdb..sysjobschedules x, msdb..sysschedules y WHERE x.schedule_id = y.schedule_id and job_id = @jobIDRabia-------------------------------------------------------------------------- Name : gera_jobs.sql -- Author : Edu F Mont November 24, 2004-- Mail : edu_dba@hotmail.com -- RDBMS : SQL Server 2000 -- Desc : This SQL statement will provide script for backup all your jobs.-------------------------------------------------------------------------- IMPORTANT : Grow-up "maximum characters per column" (for 8192) and disable "print column headers" in 'tools' / 'options' ------------------------------------------------------------------------SET NOCOUNT ONGOSET QUOTED_IDENTIFIER OFFGOdeclare @jobID nvarchar (40) declare @cmd varchar (2000) IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = '##jobids') DROP TABLE ##jobids SELECT @cmd = 'SELECT distinct (job_id) as job_id, 0 AS lido INTO ##jobids FROM msdb..sysjobs ORDER BY 1' EXEC (@cmd)WHILE EXISTS (SELECT * FROM ##jobids WHERE lido = 0 )BEGIN SELECT @jobID = job_id FROM ##jobids WHERE lido = 0 SELECT ' -- DECLARACOES DE VARIAVEIS BEGIN TRANSACTION DECLARE @jobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'''+a.name+''') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'''+a.name+'''' FROM msdb..syscategories a, msdb..sysjobs b WHERE a.category_id = b.category_id and b.job_id = @jobID SELECT '-- Delete the job with the same name (if it exists) SELECT @jobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'''+name+''') 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 '''''+name+''''' 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'''+name+''' SELECT @jobID = NULL END BEGIN' FROM msdb..sysjobs WHERE job_id = @jobID IF (SELECT COUNT(*) FROM msdb..sysjobs WHERE job_id = @jobID and notify_email_operator_id <> 0) > 1 BEGIN SELECT ' -- Adiciona o job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+', @notify_email_operator_name = N'''+d.name+''' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c, msdb..sysoperators d WHERE a.owner_sid = b.sid and a.category_id = c.category_id and d.id = a.notify_email_operator_id and job_id = @jobID END ELSE BEGIN SELECT ' -- Adiciona o job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c WHERE a.owner_sid = b.sid and a.category_id = c.category_id and a.job_id = @jobID END SELECT ' -- Adiciona o job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = '+cast (a.step_id as nvarchar (3))+', @step_name = N'''+a.step_name+''', @command = N'''+REPLACE (a.command,"'","''")+''', @database_name = N'''+ISNULL (a.database_name,'') +''', @server = N'''', @database_user_name = N'''+ISNULL (a.database_user_name, '')+''', @subsystem = N'''+a.subsystem+''', @cmdexec_success_code = '+cast (a.cmdexec_success_code as nvarchar(2))+', @flags = '+cast (a.flags as nvarchar(2))+', @retry_attempts = '+cast (a.retry_attempts as nvarchar(2))+', @retry_interval = '+cast (a.retry_interval as nvarchar(2))+', @output_file_name = N'''+ISNULL (a.output_file_name, '')+''', @on_success_step_id = '+cast (a.on_success_step_id as nvarchar(3))+', @on_success_action = '+cast (a.on_success_action as nvarchar(2))+', @on_fail_step_id = '+cast (a.on_fail_step_id as nvarchar(2))+', @on_fail_action = '+cast (a.on_fail_action as nvarchar(2))+' 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' FROM msdb..sysjobsteps a WHERE a.job_id = @jobID SELECT ' -- Adiciona o job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'''+name+''', @enabled = '+cast (enabled as nvarchar (1))+', @freq_type = '+cast (freq_type as nvarchar (2))+', @active_start_date = '+cast (active_start_date as nvarchar(10))+', @active_start_time = '+cast (active_start_time as nvarchar(10))+', @freq_interval = '+cast (freq_interval as nvarchar(3))+', @freq_subday_type = '+cast (freq_subday_type as nvarchar(5))+', @freq_subday_interval = '+cast (freq_subday_interval as nvarchar(10))+', @freq_relative_interval = '+cast (freq_relative_interval as nvarchar(5))+', @freq_recurrence_factor = '+cast (freq_recurrence_factor as nvarchar(10))+', @active_end_date = '+cast (active_end_date as nvarchar(10))+', @active_end_time = '+cast (active_end_time as nvarchar(10))+' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' --FROM msdb..sysjobschedules --WHERE job_id = @jobID FROM msdb..sysjobschedules x, msdb..sysschedules y WHERE x.schedule_id = y.schedule_id and job_id = @jobID PRINT ' -- Adiciona o 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:GO' UPDATE ##jobids SET lido = 1 WHERE job_id = @jobIDEND SET NOCOUNT OFFGOSET QUOTED_IDENTIFIER ONGOWed, 12 Aug 2009 15:04:21 GMTRabia Cinar Yukselhttp://www.sqlservercentral.com/Forums/Topic404712-462-1.aspxDoesnt seem to work with SQL2005? Breaks at referencing the "name" column of sysjobschedules since that column doesnt exist.Fri, 26 Jun 2009 10:47:13 GMTbugmenot-573553http://www.sqlservercentral.com/Forums/Topic404712-462-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/31385/"> create t-sql scripts for every jobs in Instance.</A>[/B]Sun, 30 Sep 2007 10:10:30 GMTSite Owners