February 6, 2008 at 9:38 am
Hi, I'm on the scrounge.
I need to automate a process to script out all SQLAgent jobs on a 2005 server. I understand you need to do this in sqldmo or VB, which I am not conversant in.
does anyone out there have a script\process to do this?
thanks in advance
george
---------------------------------------------------------------------
February 6, 2008 at 11:55 am
Check if this helps u.
http://technet.microsoft.com/en-us/library/ms191450.aspx
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 6, 2008 at 12:26 pm
This below link does exactly what you need.
http://www.sqlmag.com/Article/ArticleID/38790/sql_server_38790.html
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 7, 2008 at 4:00 am
Sugesh, thanks for trying but I need to script out ALL the jobs and the first URL is the SSMS method to script out a single job and the second site requires that I pay to subscribe to get the code (as its a trade mag), and its for SQL 2000 anyway (don't know if it would still work)
If anyone has code for SQL 2005 I would appreciate it.
george
---------------------------------------------------------------------
February 7, 2008 at 7:51 am
George,
Open SSMS click on jobs and in the Object Explorer Details Windows you will see a list of jobs. You can highlight all the jobs and right click --> script job as --> Create to --> new file or new query window.
This will script all selected jobs to a single file or a new query window.
February 7, 2008 at 8:00 am
Sorry did not read your post in full; I missed automated. Why does this process need to be automated? Are you moving the jobs to another server?
One option is to use the transfer jobs task in SSIS, if you are moving the jobs to another database.
February 7, 2008 at 8:15 am
Adam, thankyou so much. I thought there must be a way to do this in SSMS as it was possible in enterprise manager. Unfortunatly to me things are not as obvious in SSMS, using SSMS is a different way of thinking, which is not as intuitive to me yet as I have no history using VS. MS favouring developers again. Don't get me started on SSIS.
...and you'f think this would be mentioned by MS at http://technet.microsoft.com/en-us/library/ms191450.aspx
Now for a way to automate the scripting process anyone...................
---------------------------------------------------------------------
February 7, 2008 at 8:24 am
adam, our last posts crossed...
I need to automate it because I have a log shipped server with multiple databases, log shipping in both directions. I might need to fail over one database or all of them... so I would prefer to have the jobs scripted out so I can recreate the ones I need easily, as opposed to having them actually being loaded onto the standby server.
I could bever get the transfer job task to work in DTS, does it actually work in SSIS?
---------------------------------------------------------------------
February 7, 2008 at 9:57 am
I could bever get the transfer job task to work in DTS, does it actually work in SSIS?
I haven't had any problems with the transfer jobs task. SSIS is a much more scalable solution than its predecessor DTS; however, it has many features that make it more time consuming to learn.
February 7, 2008 at 1:58 pm
You have the zip file containing the code there hope u have missed to see the file over there.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 8, 2008 at 2:29 am
Take a look at http://www.codeplex.com/SQLServerFineBuild. The main purpose of my publishing FineBuild is to help give a 1-click install and configuration of SQL Server 2005.
However, it also contains a spScriptJobs script that can script out all jobs, or a single specified job. The output can be sent to a single file for all jobs, or each job can have its own file. There are similar scripts for other database objects. HTH
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 19, 2008 at 2:40 am
spScriptJobs does not seem to work. The proc produces the file but if you try to run the file produced there are a stack of syntax errors......anyone else with this problem?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 19, 2008 at 2:59 am
the syntax errors could be 'go' statements within the jobs, this throws out the syntax checking. replace any of these with ';' and it will work.
I have used Ed's code with success on my SQL 2000 boxes
---------------------------------------------------------------------
June 19, 2008 at 3:08 am
It may be that i'm running it on 2005 but i thought the script was built for sql 2005....?
The first issue is the fact that there are end statements without corresponding begin's:
begin
RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1)
goto QuitWithRollback
end else
execute msdb.dbo.sp_delete_job @job_name = N'JobName'
select @JobID = NULL
end
should be:
begin
RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1)
goto QuitWithRollback
end else BEGIN
execute msdb.dbo.sp_delete_job @job_name = N'JobName'
select @JobID = NULL
end
This is a quick find and replace but once these are fixed then we have issues with the very last section of the script.
instead of
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
END
commit transaction
goto EndSave
QuitWithRollback:
if (@@TRANCOUNT > 0) rollback transaction
EndSave:
it say's (exact statement here, not a typo!):
if (@@ERRORcommit transaction
goto EndSave
QuitWithRollback:
if (@@TRANCOUNT > 0) rollback transaction
EndSave:
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 20, 2008 at 2:41 am
Thanks for the heads-up about syntax errors. I am working on a new version of FineBuild and will check these scripts.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply