Copy a stored procedure from one SQL Server 2008 to another using SQL Server Management Objects

I am using the SQL Server Management Object lib in a C# application. I need to copy a stored procedure from a source server to a destination server on a different machine. I can retrieve the StoredProcedure Object back from the source server and view the object in the debugger (transferProc).

If I try 开发者_Go百科and just add the proc (transferProc) to the new server stored procedure object:

tdb.StoredProcedures.Add(transferProc);

When I do this I get an error stating:

Message "Parent property of object [dbo].[aStoredProc] does not match the collection's parent to which it is added."

If I try and change the parent and set it to the target database, I get a different error:

Message "SetParent failed for StoredProcedure 'dbo.aStoredProc'. "

InnerException {"Cannot perform the operation on this object, because the object is a member of a collection."}

How do I copy the db.StoredProcedure object to tdb.StoredProcedure on the new server?

    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Server;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer;

    string srcDB = "foo";
    string destDB = "bar";
    string proc = "aStoredProc";

    __DevSqlConnection = new SqlConnection(__DevSqlConnectionString);
    __DevSqlConnection.Open();

    __TestingSqlConnection = new SqlConnection(__TestingSqlConnectionString);
    __TestingSqlConnection.Open();

    //SMO Server object setup with SQLConnection.
    Server devServer = new Server(new ServerConnection(__DevSqlConnection));
   //Set Database 
    Database db = devServer.Databases[srcDB];

    //SMO For the Receiving Server
    Server testServer = new Server(new ServerConnection( __TestingSqlConnection ));
    //Set Database
    Database tdb = testServer.Databases[destDB];

    //Set the proc we wish to Script
    StoredProcedure transferProc = db.StoredProcedures[proc];

    //Change the parent
    transferProc.Parent = tdb;

    tdb.StoredProcedures.Add(transferProc);

I found if you create a new store procedure instance with the target database as the parent and set the procedure name and schema and then set the TextBody and TextHeader and then call the create method it will copy the stored procedure from one databse to another.

StoredProcedure procNew = new StoredProcedure(dbProd, procDEV.Name, procDEV.Schema);
procNew.TextBody = procDEV.TextBody;
procNew.TextHeader = procDEV.TextHeader;
procNew.Create();

Try this. Note that

procedureText : is the actual stored procedure code and not the name of the stored procedure. databaseName : This is the name of the database

//Create a new StoredProcedure Object
StoredProcedure sProcedure = new StoredProcedure();
//Set its Text property to the
//text of the procedure you're creating
sProcedure.Text = procedureText;
//Add the stored procedure to the database
Server.Databases.Item(databaseName, "dbo").StoredProcedures.Add(sProcedure);

I ended up getting to to work, but I'm not thrilled with it. I used the Script() Method to generate a script then had to parse out the first 2 lines, because the script when run must begin with 'CREATE or ALTER'.

using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Server;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer;

    string srcDB = "foo";
    string destDB = "bar";
    string proc = "aStoredProc";


__DevSqlConnection = new SqlConnection(__DevSqlConnectionString);
                __DevSqlConnection.Open();

                __TestingSqlConnection = new SqlConnection(__TestingSqlConnectionString);
                __TestingSqlConnection.Open();

                //SMO Server object setup with SQLConnection.
                Server devServer = new Server(new ServerConnection(__DevSqlConnection));
                //Set Database 
                Database db = devServer.Databases[srcDB];

                //SMO For the Receiving Server
                Server testServer = new Server(new ServerConnection(__TestingSqlConnection));
                //Set Database
                Database tdb = testServer.Databases[destDB];

                //Set the proc we wish to Script
                StoredProcedure transferProc = db.StoredProcedures[proc];
                transferProc.TextMode = false;
                transferProc.AnsiNullsStatus = false;
                transferProc.QuotedIdentifierStatus = false;

                //Create an SPObj for the new server.
                StoredProcedure tProc = new StoredProcedure(tdb, proc);


                //Create the Creation Script.
                ScriptingOptions so = new ScriptingOptions();
                StringCollection script = transferProc.Script(so);
                string[] scriptArray = new string[script.Count];
                script.CopyTo(scriptArray, 0);
                foreach (string line in scriptArray)
                {
                    if (line.IndexOf("CREATE", StringComparison.CurrentCulture) >= 0)
                        __SchemaScript += line + Environment.NewLine;
                }


                //Run the script against the target testing DB.
                SqlCommand cmdCreate = new SqlCommand(__SchemaScript, __TestingSqlConnection);
                cmdCreate.CommandType = CommandType.Text;
                cmdCreate.CommandTimeout = 7200;
                cmdCreate.ExecuteNonQuery();

You were close: 'Get the SP Text from the "from" server's db Dim frSP As String = getSP(dbF, theSP, FailF, paramsF) 'In sp for the "To" server and that text/script spT.TextBody = frSP spT.TextMode = False

For Params: Dim paramsF As StoredProcedureParameterCollection = spT.Parameters

If Not paramsF Is Nothing Then For Each p As StoredProcedureParameter In paramsF Dim pp As StoredProcedureParameter pp = New StoredProcedureParameter(spT, p.Name, p.DataType) spT.Parameters.Add(pp) pp = Nothing Next

    End If

HTH