Microsoft KB Archive/315505

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

Article ID: 315505

Article Last Modified on 11/17/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q315505





IN THIS TASK
SUMMARY
 * Requirements
 * Create a Demonstration Application
 * Add Code to the Application
 * Verification
 * Alternatives

REFERENCES



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.
 * 1) On the Project menu, click References, and then select Microsoft SQLDMO Object Library. Click OK.
 * 2) 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
  In the module window, write a Main subroutine as follows: Sub Main End Sub   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   Add code in Main, to connect to the source SQL Server. In the Connect function, replace &quot;source&quot;, &quot;username&quot;, and &quot;password&quot; with the actual SQL Server name and user login details: Set MyServer = New SQLServer MyServer.Connect &quot;source&quot;, &quot;username&quot;, &quot;password&quot;   Add code to get the byroyalty stored procedure in the pubs database: Set MyDatabase = MyServer.Databases(&quot;pubs&quot;) Set MyStoredProcedures = MyDatabase.StoredProcedures Set MyProcedure = MyStoredProcedures(&quot;byroyalty&quot;)   Add code to clean up the local variables: Set MyStoredProcedures = Nothing Set MyDatabase = Nothing Set MyServer = Nothing   Add code to connect to the target SQL Server. In the Connect function, replace &quot;target&quot;, &quot;username&quot;, and &quot;password&quot; with the actual SQL Server name and user login details: Set MyServer = New SQLServer MyServer.Connect &quot;target&quot;, &quot;username&quot;, &quot;password&quot; </li>  Add code to add the byroyalty stored procedure to the pubs database in the target SQL Server: Set MyDatabase = MyServer.Databases(&quot;pubs&quot;) Set MyProcedureNew = New StoredProcedure MyProcedureNew.Text = MyProcedure.Text MyDatabase.StoredProcedures.Add MyProcedureNew </li>  Add code to clean up the local variables: Set MyStoredProcedures = Nothing Set MyDatabase = Nothing Set MyServer = Nothing Set MyProcedure = Nothing Set MyProcedureNew = Nothing </li> Save SPTransfer.bas.</li></ol>

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 &quot;Copy SQL Server Objects Task&quot; 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 &quot;How to generate a script (Enterprise Manager)&quot; 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 &quot;References&quot; section of this article.

back to the top