Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Generating Scripts for SQL Server Jobs
SQL-DMO gives you a way to list jobs running on SQL Server and obtain their scripts.
Microsoft's Sql Server Development Team
May 19, 2003
1 Min Read
How can I generate scripts for all jobs running on my SQL Server? I want one script that includes all jobs and one script for each individual job.
You can use SQL Distributed Management Objects (SQL-DMO) to obtain a list of jobs. Use the JobServer object and iterate through the Jobs collection. To obtain each job's script, you can call the Scripts method for that job. Here's what you should do:
Start Visual Basic (VB) 6.0.
Start a standard EXE project.
Add a command button.
Double-click the new command button to get the click event code window.
From the main VB menu, select Project, References. Scroll through the References list to find the Microsoft SQLDMO Object Library and add it. Try to use the .dll file that shipped with the version of SQL Server you're running the .dll against. You can find the .dll version by looking at the location the .dll comes from in the window you have open.
To fetch all of your jobs, paste the code that Listing 4 shows into the click handler for the button. Review the Change if required lines, then run the program.
If you want to script jobs interactively, you can do so from Enterprise Manager by right-clicking jobs and choosing All Tasks, Generate SQL Script.
About the Author(s)
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
.jpg?width=700&auto=webp&quality=80&disable=upscale)