Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script all jobs on SQL 2005/2008 Server Expand / Collapse
Author
Message
Posted Sunday, February 01, 2009 6:34 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
Hi,

I need to automate scripting of all jobs on a SQL 2005/2008 server. I can't use any Integration Services packages, and apparently SCPTXFR.EXE is no longer present in SQL Server 2005.

Just wondering if there is an existing script for this task, so I could put it as a job step?

Thanks.



Post #647768
Posted Monday, February 02, 2009 7:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 12:36 PM
Points: 78, Visits: 82
In sql server 2005, you can use sql management studio --> select the Job folder in object explore --> select all jobs in object explore details window--> right click and script all the jobs. Some modifications may need if you want to deploy in another computer. Hope it helps.
Post #648030
Posted Monday, February 02, 2009 3:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
No, this is not what I want. I don't want a manual process, I want automatic. E.g. a job running every night and scripting all the jobs into a text file.


Post #648419
Posted Tuesday, February 03, 2009 6:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:27 AM
Points: 1,757, Visits: 2,118
you can use one of the dot net languages with SMO to script the jobs. This is a snippet from something I did in VB.Net. Please keep in mind that I am not very good with VB. Of course you will need to create all your variable, objects, streamwriter, etc.

There are quite a lot of examples on the net. It's the only way I could do it since I'm not a dot net person.


wf.WriteLine()
wf.WriteLine("-- **** Create Jobs")
wf.WriteLine()

Dim jb As Agent.Job
For Each jb In svr.JobServer.Jobs
smoObjects = New Urn(0) {}
smoObjects(0) = jb.Urn
scrp.Options.IncludeIfNotExists = True
Dim sc As StringCollection
sc = scrp.Script(smoObjects)
Dim st As String
For Each st In sc
wf.WriteLine(st)
wf.Flush()
Next
Next



MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #648762
Posted Tuesday, February 03, 2009 4:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
I am not good with VB either. In fact I have no knowledge of it at all, because I am not a developer. I was just wondering if there is a TSQL script already developed, so I don't have to develop it myself.


Post #649424
Posted Wednesday, February 04, 2009 5:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:27 AM
Points: 1,757, Visits: 2,118
The snippet is part of a much larger program that scripts out every object in a database so it would be hard to break down.

Maybe someone who is good with Powershell can help. From some of the PS code I've seen, using the PS SQL provider, someone can probably do this as a PS script.


MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #649707
Posted Wednesday, March 11, 2009 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 311, Visits: 1,080
Hello everyone,

I believe this will make everyone happy and there is not coding involved. Just search for SQLScripter. A gentleman has create a neat application to script out everything or just some things including jobs. Just modify the confiig file and execute.

That's it.

Rudy



Post #673226
Posted Wednesday, March 11, 2009 7:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 5,849, Visits: 12,584
Rudy Panigas (3/11/2009)
Hello everyone,

I believe this will make everyone happy and there is not coding involved. Just search for SQLScripter. A gentleman has create a neat application to script out everything or just some things including jobs. Just modify the confiig file and execute.

That's it.

Rudy


can you provide a URL, search on sqlscripter produced nothing obvious


---------------------------------------------------------------------

Post #673242
Posted Wednesday, March 11, 2009 7:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 311, Visits: 1,080
I just Googled it. http://www.sqlscripter.com/


Post #673262
Posted Wednesday, March 11, 2009 8:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:55 AM
Points: 177, Visits: 545
Hi, I use the following as an active x script from a job on SQL 2000:

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "##Server_Name##"

strFilename = "C:\JOBS.sql"

For Each oJB In conServer.JobServer.Jobs
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Post #673299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse