SQLDMO and Stored Procedures

Question

SQLDMO and Stored Procedures

runelords

Ten Centuries

Points: 1275
More actions
May 18, 2004 at 12:06 pm

#85670

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

Arthur Lorenzini

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

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

SQLShark SSCrazy Points: 2214 More actions · Answer 1

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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

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

runelords Ten Centuries Points: 1275 More actions · Answer 3

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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

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.

runelords Ten Centuries Points: 1275 More actions · Answer 5

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