script all MSDB jobs in TSQL

I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/...790/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:

> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:

> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>