SQLDMO and Stored Procedures

  • Does anybody have an example of how you can use SQLDMO to copy stored procedures from one server to another?


    Arthur Lorenzini

  • I found this on the web in a thread, it beat writing the whole thing myself. I added a bunch of WSH to create and dump to a timestamped file. The script will script off the whole db and all objects to the file. I removed comments so it will fly in a sql agent job and can be scheduled to run weekly.

    Put this in a .vbs script for manual execution or cut into a sql agent activeX job task...You will need to edit these lines first:

    DbName = "Pubs"

    strFilename = "c:\Test\"&DbName&"_dbScript_"&TimeStamp2&".sql"

    oSQLServer.Connect "SQLSERVERNAMEHERE"

    Copy below here:

     

    Dim strFilename

    DIM CurrentYear

    DIM CurrentMonth

    Dim CurrentDay

    DIM TimeStamp2

    DIM DbName

    Dim strScriptPath

    CurrentYear = DatePart("yyyy", Date)

    CurrentMonth = DatePart("M",Date)

    Currentday = DatePart("D",Date)

    IF CurrentMonth < 10 then

    CurrentMonth = "0"&CurrentMonth

    TimeStamp2 = CurrentMonth & CurrentDay & CurrentYear

    END If

    DbName = "Pubs"

    strFilename = "c:\Test\"&DbName&"_dbScript_"&TimeStamp2&".sql"

    strScriptPath = strFilename   

    Const ioModeAppend = 8

    Const SQLDMOScript_DatabasePermissions = 32

    Const SQLDMOScript_PrimaryObject = 4

    Const SQLDMOScript_Default = 4

    Const SQLDMOScript_AppendToFile = 256

    Const SQLDMOScript_DRI_All = 532676608

    Const SQLDMOScript_Drops = 1

    Const SQLDMOScript_IncludeHeaders = 131072

    Const SQLDMOScript_Indexes = 73736

    Const SQLDMOScript_ObjectPermissions = 2

    Const SQLDMOScript_OwnerQualify = 262144

    Const SQLDMOScript_ToFileOnly = 64

    Const SQLDMOScript_Triggers = 16

    Const SQLDMOScript_Permissions = 34

    Const SQLDMOScript2_Default = 0

    Const SQLDMOScript2_AnsiFile = 2

    Const SQLDMOScript2_UnicodeFile = 4

    Const SQLDMOScript2_ExtendedProperty = 4194304

    Const SQLDMOScript2_NoFG = 16

    Const SQLDMOScript2_NoWhatIfIndexes = 512

    Const SQLDMOCopyData_Append = 2

    Const SQLDMOCopyData_False = 0

    Const SQLDMOCopyData_Replace = 1

    Const SQLDMOXfrFile_SingleFile = 2

    Dim oSQLServer, oDatabase

    Dim oTransfer

    Set oSQLServer = CreateObject ("SQLDMO.SQLServer2")

    oSQLServer.LoginSecure = True

     

    oSQLServer.Connect "SQLSERVERNAMEHERE"

    Set oDatabase = oSQLServer.Databases(DbName)

    Set oTransfer = CreateObject("SQLDMO.Transfer2")

    oTransfer.IncludeDb=True

    oTransfer.CopyAllObjects = True

    oTransfer.CopyData = SQLDMOCopyData_False

    oTransfer.CopySchema = True

    oTransfer.DropDestObjectsFirst = True

    oTransfer.IncludeDependencies = True

    oTransfer.IncludeLogins = False

    oTransfer.IncludeUsers = True

    oTransfer.ScriptType = SQLDMOScript_Permissions Or _

    SQLDMOScript_DRI_All Or _

    SQLDMOScript_Drops Or _

    SQLDMOScript_Indexes Or _

    SQLDMOScript_OwnerQualify Or _

    SQLDMOScript_PrimaryObject Or _

    SQLDMOScript_Triggers

    oTransfer.Script2Type = SQLDMOScript2_AnsiFile Or _

    SQLDMOScript2_ExtendedProperty Or _

    SQLDMOScript2_NoFG

    oDatabase.ScriptTransfer oTransfer, SQLDMOXfrFile_SingleFile, strScriptPath

    Set oDatabase = Nothing

    oSQLServer.Close

    Set oSQLServer = Nothing

  • Why not use the DTS "Copy SQL Objects" function and specify only the Stored Procedures? Saves writing a lot of code.

     

  • This is being done within an application and I know I can kick off a DTS package using stored procedures but my problem lies in I don't always want to copy all the stored procedures all the time. The use may select certain ones from a listbox. How could I use "Copy SQL Objects" function to copy the selected stored procedures.


    Arthur Lorenzini

  • If the application is VB you can load the package and manipulate the properties of the Copy SQL Objects task. I'd set the task up originally to copy all the SP's, get the list of SP's from the task properties, display it to the user, then update the properties based on their selections. Sorry I don't have code right at hand to show you, but as I said, there's lots of code on the web and at MS (MSDN) on manipulating DTS packages in VB.

     

  • This is is a VB.NET & ASP.NET application. I used the code you supplied to copy a stored procedure from one server to another with in a ActiveX Script in a DTS package but when I went to incorporate it into my ASP.Net project  but I would like to beable to do it directly instead of scripting it first. Is this possible?


    Arthur Lorenzini

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply