HOW TO: Use the SQL Distributed Management Objects Model to Programmatically Transfer SQL Server Stored Procedures (315505)

SUMMARY

This article provides the code required to transfer a SQL Server stored procedure from one server to another, by using the SQL Distributed Management Objects (SQL-DMO) model.

back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge and service packs you have to have.

• Microsoft Visual Basic 6.0, or later.
• Microsoft SQL Server 6.5, or later.

Prior Knowledge required

• Working knowledge of Visual Basic.
• Familiarity with SQL Server.

back to the top

Create a Demonstration Application

1. Start Visual Basic 6.0 and create a new project of type Standard EXE.
2. In the Properties dialog box, right-click Form1, and then click Remove Form1. A dialog box appears that prompts you to save the form. Click No.
3. Save the project as SPTransfer.vbp.
4. On the Project menu, click Project1 Properties. In the Project Name field, type:
   
   SPTransfer
   
   In the Startup Object drop-down list box, make sure Sub Main is selected. Click OK.
5. On the Project menu, click References, and then select Microsoft SQLDMO Object Library. Click OK.
6. On the Project menu, click Add Module. In the Add Module dialog box, click OK. Save Module1 as SPTransfer.bas.

back to the top

Add Code to the Application

1. In the module window, write a Main subroutine as follows:

   Sub Main()
   End Sub
   					

2. Inside Main, declare the following variables:

   Dim MyServer As SQLServer   
   Dim MyDatabase As Object
   Dim MyStoredProcedures As Object
   Dim MyProcedure As Object
   Dim MyProcedureNew As Object
   					

3. Add code in Main, to connect to the source SQL Server. In the Connect function, replace "source", "username", and "password" with the actual SQL Server name and user login details:

   Set MyServer = New SQLServer
   MyServer.Connect "source", "username", "password"
   					

4. Add code to get the byroyalty stored procedure in the pubs database:

   Set MyDatabase = MyServer.Databases("pubs")
   Set MyStoredProcedures = MyDatabase.StoredProcedures
   Set MyProcedure = MyStoredProcedures("byroyalty")
   					

5. Add code to clean up the local variables:

   Set MyStoredProcedures = Nothing
   Set MyDatabase = Nothing
   Set MyServer = Nothing
   					

6. Add code to connect to the target SQL Server. In the Connect function, replace "target", "username", and "password" with the actual SQL Server name and user login details:

   Set MyServer = New SQLServer
   MyServer.Connect "target", "username", "password"
   					

7. Add code to add the byroyalty stored procedure to the pubs database in the target SQL Server:

   Set MyDatabase = MyServer.Databases("pubs")
   Set MyProcedureNew = New StoredProcedure
   MyProcedureNew.Text = MyProcedure.Text
   MyDatabase.StoredProcedures.Add MyProcedureNew
   					

8. Add code to clean up the local variables:

   Set MyStoredProcedures = Nothing
   Set MyDatabase = Nothing
   Set MyServer = Nothing    
   Set MyProcedure = Nothing
   Set MyProcedureNew = Nothing
   					

9. Save SPTransfer.bas.

back to the top

Verification

1. Open Enterprise Manager from the Microsoft SQL Server program group. Select the target SQL Server, and then remove the byroyalty stored procedure from the pubs database.
2. Build, and then run your SPTransfer Visual Basic application.
3. In Enterprise Manager, select the target SQL Server again. Verify that the byroyalty stored procedure has reappeared in the pubs database.

back to the top

Alternatives

SQL Server stored procedures can be transferred between servers by using Data Transformation Services (DTS). Refer to the "Copy SQL Server Objects Task" topic in SQL Server Books Online for detailed information. You can also use the Generate SQL Script feature of the SQL Server Enterprise Manager to obtain a CREATE stored procedure script, and then apply the script on the destination database to create the procedure. Refer to the "How to generate a script (Enterprise Manager)" topic in SQL Server Books Online. However, preparing a programmatic solution by using SQL DMO may be preferable if the program will be used by a user who must not use the SQL Server tools to access the database and needs to perform administrative functions automatically.

For more information about how to run a DTS package in Visual Basic, refer to the "References" section of this article.

back to the top

REFERENCES

SQL Server 2000 Books Online; topics: "Copy SQL Server Objects Task"; "How to generate a script (Enterprise Manager)" For additional information, click the following article number to view the article in the Microsoft Knowledge Base: back to the top