Microsoft KB Archive/321835

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

Article ID: 321835

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 2
 * Microsoft SQL Server 7.0 Service Pack 3
 * Microsoft SQL Server 7.0 Service Pack 4

-



This article was previously published under Q321835



IN THIS TASK
SUMMARY
 * Use Transact-SQL to Script Out Jobs

REFERENCES



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 &quot;Scripting Jobs&quot; 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:  Open Query Analyzer. The Connect to SQL Server dialog box appears. 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. Click OK. The Connect to SQL Server dialog box closes, and Query Analyzer opens.  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 + ',&quot;' + @FileName +'&quot;,' + @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  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

