'************************************************************************ ' Copy all stored procedures in source database to target database ' Clears any existing procedures on target before the copy. ' ' Expects 4 global variables ' Source SQL Server ' Source Database ' Target SQL Server ' Target Database ' ' explicitly excludes system procedures and procedures beginning ' with "sp_MS..." (replication procedures that are user objects) ' ' Bill Wunder '************************************************************************ Option Explicit Const SQLDMOScript_Drops = 1 Const SQLDMOScript_ObjectPermissions = 2 Const SQLDMOScript_Default = 4 Const SQLDMOScript_OwnerQualify = 262144 Const SQLDMOScript2_Default = 0 Function Main() Dim sSourceSQLServer, sSourceDB Dim sTargetSQLServer, sTargetDB Dim oSourceSQLServer, oSourceDB, oSourceProcedure Dim oTargetSQLServer, oTargetDB, oTargetProcedure Dim iScriptType, iScript2Type iScriptType = SQLDMOScript_Drops Or _ SQLDMOScript_ObjectPermissions Or _ SQLDMOScript_OwnerQualify Or _ SQLDMOScript_Default iScript2Type = SQLDMOScript2_Default sSourceSQLServer = DTSGlobalVariables("Source SQL Server") sSourceDB = DTSGlobalVariables("Source Database") sTargetSQLServer = DTSGlobalVariables("Target SQL Server") sTargetDB = DTSGlobalVariables("Target Database") If sSourceSQLServer = sTargetSQLServer _ And sSourceDB = sTargetDB Then Exit Function End If Set oTargetSQLServer = CreateObject("SQLDMO.SQLServer") oTargetSQLServer.LoginSecure = True 'trusted oTargetSQLServer.Connect sTargetSQLServer Set oTargetDB = oTargetSQLServer.Databases.Item(sTargetDB) ' need a file system instance and a DMO instance Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer") oSourceSQLServer.LoginSecure = True 'trusted oSourceSQLServer.Connect sSourceSQLServer Set oSourceDB = oSourceSQLServer.Databases.Item(sSourceDB) 'clear all user stored procedures out of the replica For Each oTargetProcedure In oTargetDB.StoredProcedures If Not oTargetProcedure.SystemObject _ And Not (Mid(oTargetProcedure.Name, 1, 5) = "sp_MS") Then oTargetProcedure.Remove End If Next 'add all procedures from the primary to the replica For Each oSourceProcedure In oSourceDB.StoredProcedures If Not oSourceProcedure.SystemObject _ And Not (Mid(oSourceProcedure.Name, 1, 5) = "sp_MS") Then Set oTargetProcedure = CreateObject("SQLDMO.StoredProcedure") oTargetProcedure.Name = oSourceProcedure.Name oTargetProcedure.Text = oSourceProcedure.Script(iScriptType, , iScript2Type) oTargetDB.StoredProcedures.Add oTargetProcedure End If Next 'oSourceProcedure oSourceSQLServer.DisConnect Set oSourceSQLServer = Nothing oTargetSQLServer.DisConnect Set oTargetSQLServer = Nothing Main = DTSTaskExecResult_Success End Function