May 18, 2004 at 12:06 pm
Does anybody have an example of how you can use SQLDMO to copy stored procedures from one server to another?
Arthur Lorenzini
May 18, 2004 at 2:40 pm
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
May 20, 2004 at 6:21 am
Why not use the DTS "Copy SQL Objects" function and specify only the Stored Procedures? Saves writing a lot of code.
May 20, 2004 at 7:14 am
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
May 20, 2004 at 7:22 am
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.
May 21, 2004 at 9:25 am
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