HOW TO: Use sp_OA Stored Procedures and SQL Distributed Management Objects (SQL-DMO) to Script Out Jobs in SQL Server (321835)

SUMMARY

This article describes how to use Transact-SQL and SQL Distributed Management Objects (SQL-DMO) to script out jobs to a file, in Transact-SQL format.

You can also use the SQL Enterprise Manager generate script feature to get the job script. For more information, see the "Scripting Jobs" topic in SQL Server 2000 Books Online. This article only demonstrates how to use the sp_OA* stored procedures to call the SQL-DMO objects to script jobs.

back to the top

Use Transact-SQL to Script Out Jobs

The steps in this section demonstrate how to script out jobs. The sample code creates an instance to the object SQLDMO.SQLServer, connects to the database, and then runs the Jobs.Script command. The result is saved to a file specified by the @FileName parameter.

You must modify the following properties so that they fit the server you are using:

@FileName - The file path and name that will contain the Transact-SQL Job script.

@UserName and @Password - A SQL Server username and password with proper permissions.

To use Transact-SQL and run script out jobs, follow these steps:

1. Open Query Analyzer. The Connect to SQL Server dialog box appears.
2. In the SQL Server text box, type the SQL Server name. Under the Connect Using, click to select Windows or SQL Server Authentication. If you select SQL Server Authentication, type a username and a password that has appropriate permissions.
3. Click OK. The Connect to SQL Server dialog box closes, and Query Analyzer opens.
4. In the Query Editor window, type the following code:

   	
   --sp_OA params
   DECLARE @cmd varchar(255) -- Command to run
   DECLARE @oSQLServer int -- OA return object 
   DECLARE @hr int -- Return code
   
   --User params
   DECLARE @FileName varchar(200) -- File name to script jobs out 
   DECLARE @Server varchar(30) -- Server name to run script on. By default, local server.
   
   --SQL DMO Constants
   DECLARE @ScriptType varchar(50)
   DECLARE @Script2Type varchar(50)
   SET @ScriptType = '327'  -- Send output to file, Transact-SQL, script permissions, test for existence, used quoted characters.
   SET @Script2Type = '3074'  -- Script Jobs, Alerts, and use CodePage 1252.
   
   --Set the following properties for your server
   SET @FileName = 'c:\sqlJobs.sql'
   SET @Server =  @@SERVERNAME
   
   --CREATE The SQLDMO Object
   EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
   
   --Set Windows Authentication
   EXEC @hr = sp_OASetProperty @oSQLServer, 'LoginSecure', TRUE
   
   --Connect to the Server
   EXEC @hr = sp_OAMethod @oSQLServer,'Connect',NULL,@server 
   
   --Script the job out to a text file
   SET @cmd = 'Jobserver.Jobs.Script(' + @ScriptType + ',"' + @FileName +'",' + @Script2Type + ')'
   EXEC @hr = sp_OAMethod @oSQLServer, @cmd 
   
   --Close the connection to SQL Server
   --If object is not disconnected, the processes will be orphaned.
   EXEC @hr = sp_OAMethod @oSQLServer, 'Disconnect'
   
   --Destroy object created.
   exec sp_OADestroy @oSQLServer
   					

5. On the Query menu, click Execute. The query will run and create a Transact-SQL file of the SQL Server jobs found at @FileName.

back to the top