How to script Replication objects using RMO

• me_balaji

  SSC Eights!

  Points: 923

  More actions

  August 25, 2008 at 1:21 pm

  #96384

  I need to script to file all the statements necessary to re-create existing Publications, and to delete existing publications. I had this working in SQL 2000 using DMO, I am struggling a bit with the new RMO. My problem is with the ScriptOptions. I need one file for each publication that has the sp_addpublication, the snapshot, the add article and add subscription. The exact way is it produced with the GUI.

  here is what I have so far. I siumply get the ap_addpublication line with nothing else. I have tried the IncludeAll option and I get nothing.

  ' Create connection to local server

  Dim ServerConnection = New ServerConnection("MyServer")

  ''Dim File As File

  Try

  ' connect to publisher

  ServerConnection.LoginSecure = True

  ServerConnection.Connect()

  Dim ReplicationServer = New ReplicationServer(ServerConnection)

  Dim ReplicationDatabases = ReplicationServer.ReplicationDatabases

  Dim ReplicationDatabase As ReplicationDatabase

  Dim TransactionalPublication As TransPublication

  For Each ReplicationDatabase In ReplicationDatabases

  If ReplicationDatabase.HasPublications Then

  For Each TransactionalPublication In ReplicationDatabase.TransPublications

  FileName = "c:\temp\" & TransactionalPublication.Name & ".sql"

  File.WriteAllText(FileName, TransactionalPublication.Script(ScriptOptions.Creation))

  Next

  End If

  Next

  Catch ex As Exception

  Finally

  ServerConnection.Disconnect()

  End Try

• me_balaji

  SSC Eights!

  Points: 923

  More actions

  August 28, 2008 at 8:24 am

  #864431

  I finally found the solution by myself.

  ScriptOptions.Creation Or ScriptOptions.IncludeAll Xor ScriptOptions.IncludeReplicationJobs

  does the trick.

  TransactionalPublication.Script(ScriptOptions.Creation Or ScriptOptions.IncludeAll Xor ScriptOptions.IncludeReplicationJobs)

  generates the script. I was missing bitwise operators (Or /Xor).

  Thanks everyone for your time.