Programming Replication

• psullivan

  SSChasing Mays

  Points: 604

  More actions

  January 14, 2003 at 3:21 pm

  #101352

  Hey All, I wrote this with tons of help, Thanks Andy. This is the one in use now, was hoping you all could take a gander and help me find any problems. I'd really like to find a better way to run the snapshot agent. I tried using the SQLActiveX objects (http://support.microsoft.com/default.aspx?scid=kb;%5bLN%5d;319647) Here's the Code!

  '**********************************************************************

  ' Visual Basic ActiveX Script

  '************************************************************************

  Function Main()

  Dim oRDB 'As SQLDMO.ReplicationDatabase

  Dim oDb 'As SQLDMO.Database

  Dim oPub 'As SQLDMO.MergePublication

  Dim oArticle 'As SQLDMO.MergeArticle

  'create standard server object first

  Set oserver = CreateObject("SQLDMO.SQLServer")

  With oserver

  .LoginSecure = True

  .Connect DTSGlobalVariables("srvName").Value

  End With

  set oDB = oserver.Databases("DTS_Store")

  'get this to be a little closer to where we will do the work

  Set oRDB = oserver.Replication.ReplicationDatabases("DTS_Store")

  'pub normally has same name as db, doesn't have to though

  Set oPub = CreateObject("SQLDMO.MergePublication2")

  set oPub = oRDB.MergePublications("DTS_Store")

  nTBLCount = 0

  For Each oTbl in oDB.Tables

  strTblName = oTbl.Name

  strSql = "Select * From sysmergearticles Where [name] = '" & strTblName & "'"

  set oResults = oserver.Databases("DTS_Store", "dbo").ExecuteWithResults(strSQL)

  if oResults.Rows = 0 And oTbl.SystemObject = False then

  'add 1 to the new table counter

  nTBLCount = nTBLCount + 1

  'table is not a merge article need to add it

  'create the article

  Set oArticle = CreateObject("SQLDMO.MergeArticle")

  With oArticle

  .Name = strTblName

  .SourceObjectName = strTblName

  .SourceObjectOwner = "dbo"

  End With

  'add it to the collection

  oPub.MergeArticles.Add oArticle

  'clean up

  Set oArticle = Nothing

  End If

  Next

  'get the snaposhot job id

  set oJob = oserver.JobServer.GetJobByID(oPub.SnapshotJobID)

  If oJob.CurrentRunStatus = 4 and nTBLCount > 0 Then

  'job isnt running start the job

  oJob.Invoke

  'need to waste some time...

  oDB.ExecuteImmediate("waitfor delay '00:00:10'")

  oserver.JobServer.Refresh

  oJob.Refresh

  ''now need a time loop to watch for job completion b4 we reinitialize the subscriptions

  jStat = 0

  Do While jStat = 0

  'need to waste some time...

  oDB.ExecuteImmediate("waitfor delay '00:00:10'")

  oserver.JobServer.Refresh

  oJob.Refresh

  If oJob.CurrentRunStatus = 4 then 'job is completed

  jStat = 1

  End if

  loop

  End If

  'reinitialize the subscriptions

  oPub.ReInitializeAllSubscriptions

  'run the merge agent

  'resync the subscriptions

  For Each oMSub in oPub.MergeSubscriptions

  'here we resync each merge subscription

  oPub.ReSynchronizeSubscription oMSub.Name,oMSub.SubscriptionDB,0

  Next

  'clean up

  Set oPub = Nothing

  Set oRDB = Nothing

  oserver.DisConnect

  Set oserver = Nothing

  Main = DTSTaskExecResult_Success

  End Function

• January 17, 2003 at 8:00 am

  This was removed by the editor as SPAM