SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to generate scripts for all agent jobs?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 03/13/2008 :  15:15:25  Show Profile  Reply with Quote
I have about 20 agent jobs.
How to generate scripts for all agent jobs?

X002548
Not Just a Number

15586 Posts

Posted - 03/13/2008 :  15:51:06  Show Profile  Reply with Quote
What version of SQL Server?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 03/13/2008 :  16:44:11  Show Profile  Reply with Quote
2005
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/13/2008 :  23:36:41  Show Profile  Reply with Quote
Need script one by one in sql2k5 as I know.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 03/14/2008 :  01:29:40  Show Profile  Reply with Quote
run SSMS - highlight and select all the jobs - right click - script job as


KH
Time is always against us

Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/14/2008 :  23:02:22  Show Profile  Reply with Quote
Got it, have to do it in object explorer details panel.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 03/15/2008 :  00:07:59  Show Profile  Reply with Quote
why you need to script all jobs? are you migrating-if so Please do it from SSIS packages -Transfer job task.
Go to Top of Page

NancyD
Starting Member

2 Posts

Posted - 03/24/2008 :  11:51:13  Show Profile  Reply with Quote

I need to create a routine job that will script all jobs on our databases.

We need this as a backup and for documentation - any scripts for that ?????

Thanks in advance
Go to Top of Page

PABluesMan
Starting Member

26 Posts

Posted - 03/25/2008 :  14:44:15  Show Profile  Reply with Quote
You're going to have to use ActiveX to call SQL-DMO to do this. Create an executable that will generate a script for a single job with the job name as a parameter, then call that from within a loop in a SQL proc. Or you could have the executable perform the looping. your call.
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 03/25/2008 :  15:32:55  Show Profile  Reply with Quote
Here is a query I wrote to produce a friendly quick schedule of all agent jobs. I know its not exactly what you need, but you could use it as a starting point.



select 
	j.name,
    j.enabled,
	case freq_type
		when 1    then 'Once'
		when 4    then 'Daily'
		when 8    then 'Weekly on '
			+    case freq_interval & 2 when 2 then 'M' else '' end 
			+    case freq_interval & 4 when 4 then 'Tu' else '' end
			+    case freq_interval & 8 when 8 then 'W' else '' end 
			+    case freq_interval & 16 when 16 then 'Th' else '' end
			+    case freq_interval & 32 when 32 then 'F' else '' end
			+    case freq_interval & 64 when 64 then 'Sa' else '' end
			+    case freq_interval & 1 when 1 then 'Su' else '' end
		when 16   then 'Monthly on day ' + convert(varchar(2), freq_interval)
		when 32   then 'Monthly '
			+ case freq_relative_interval 
				when 1 then 'Every First '
				when 2 then 'Every Second '
				when 4 then 'Every Third '
				when 8 then 'Every Fourth '
				when 16 then 'Every Last '
			end
			+ case freq_interval	
				when 1 then 'Sunday' 
				when 2 then 'Monday'
				when 3 then 'Tuesday' 
				when 4 then 'Wednesday' 
				when 5 then 'Thursday' 
				when 6 then 'Friday' 
				when 7 then 'Saturday' 
				when 8 then 'Day' 
				when 9 then 'Week day'
				when 10 then 'Weekend day'
			end	
		when 64   then 'Startup'
		when 128 then 'Idle'
		else 'Error'
	end as 'Schedule',	

	-- Job sub-frequency
	case freq_subday_type
		when 1    then 'one time'
		when 2    then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
		when 4    then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
		when 8    then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
	end as 'Frequency',

	-- Job start time
	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) +  ':' +
	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' +
	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as 'Start',

	-- Job end time
	case freq_subday_type
		when 1  then NULL
		else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) +  ':' +
				substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)	+ ':' +
				substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) 
	end as 'End'

from msdb.dbo.sysjobs j join msdb.dbo.sysjobschedules s on j.job_id = s.job_id 
join msdb.dbo.sysschedules h on s.schedule_id = h.schedule_id
order by j.name



- Eric
Go to Top of Page

NancyD
Starting Member

2 Posts

Posted - 04/01/2008 :  14:07:31  Show Profile  Reply with Quote
Oh that looks Great - Thanks Eric !!!
Go to Top of Page

BrianBeall94706
Starting Member

1 Posts

Posted - 05/02/2008 :  13:45:48  Show Profile  Reply with Quote
I had this problem too and just stumbled on a simple way to do it using Management Studio, Follow these steps:
1. Click on Jobs
2. For 2005 click on the Summary Tab, 2008 Object Explorer Details Tab
3. Select All The Jobs you want to script in the pane click on in step 2
4. Right Mouse click and select Script Job As...
5. Proceed as you would have to Script a single job
Hope I save a few people from pulling there hair out. On one web site I saw where someone paid to have a program written to do this.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000