Automatically create scripts for all SQL Server Jobs

Question

Currently I'm trying to automatically generate a create script for all my SQL jobs of a MS SQL2005 Server.

• One method I found was done manually http://msdn.microsoft.com/en-us/library/ms191450.aspx
• A second method I found could be done automatically but I don't have direct access to the SQL server. http://relatedterms.com/thread/1916663/Can%20I%20script%20out%20SQL%20Server%20jobs%20programmatically

Does anyone know a good TSQL statement or a simple program for this?

Answer 1

I maintain files of sql for each table then use cygwin to run a shell script that cats them together then run it on the server.

#!/bin/bash
# Please add new creation scripts and meta data scripts to the script below.
# To be able to run this script don't forget to chmod 744 it.
# Make sure the create script are saved using the ANSI encoding and not unicode etc.

master_script="create_all.sql"
#path="C:\\"

#cd $path

cat *.sql >$master_script

notepad $master_script

#rm $master_script

Answer 2

After some further investigation I found a good script that did the job Generate-Scripts-for-SQL-Server-Objects

The code below is what I needed:

DECLARE @object int
exec sp_OACreate 'SQLDMO.SQLServer', @object OUT
exec sp_OASetProperty @object, 'LoginSecure', TRUE
exec sp_OAMethod @object,'Connect(sqltest)'
exec sp_OAMethod @object, 'Jobserver.Jobs().Script()'
exec sp_OADestroy @object

Thanks for the help anyway