Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Distribution Synchronisation Error Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2014 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:18 PM
Points: 3, Visits: 27
I have been attempted to setup a transactional replication across two servers.

I am using two separate Virtual Machines. One is running Windows Server 2008 with SQL Server 2008 R2 Standard. One is running Windows Server 2012 with SQL Server 2012.

I have been using a guide from Microsoft's website to setup replication data between two connected servers but have run into some errors which I have been unable to fix through solutions found online.

Below are some screenshots taken from the server in hope of showing required data or information where a blatantly obvious mistake could be seen.

Any and all suggestions are very much appreciated.




- This is the main error I am currently getting when attempting to View Synchronization Status.



- There is a second error I had encountered previously in other iterations of settings. I have been unable to replicate this.



- Here is the information provided at the end of the current Subscription Wizard setup where the original error was produced.



- Showing initial success after wizard has finished.



- Publication Properties from the right click context menu.



- Publication Access List


- Users inside of Northwind database on Subscriber end


If any more information is required for help to be delivered please ask, I'm inexperienced with SQL Server software studios and I am quite unsure what the issue could be.

Thanks for any time and help taken with this question!
Post #1595496
Posted Wednesday, July 23, 2014 11:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
The errors are largely from SQL Agent. They are saying you can not start a job that is currently running. It is nothing to worry over.
Post #1595621
Posted Thursday, July 24, 2014 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:18 PM
Points: 3, Visits: 27
Hey there!

Sorry again, completely new to SQL Server structure in general, when checking inside of the Northwind Database on the Test-2012 server it seems to have done no initial sync of data.

Would there be a reason that this could have happened and the transactional replication still working?

Thanks again for the help!

Post #1595838
Posted Thursday, July 24, 2014 7:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
Check to see if the snapshot job has run. If you right click on the replication in Management Studio, you should see an option for View Snapshot Agent Status.
Post #1595890
Posted Thursday, July 24, 2014 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:18 PM
Points: 3, Visits: 27
Couldn't find the exact option but launched Replication Monitor and went to the agents tab and found this



Total list of the error

Error messages:
Message: Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Command Text: sp_MSactivate_auto_sub
Parameters: @publication = Northwind
@article = %
@status = initiated

Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection)
at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 3933)
Get help: http://help/3933
Server WIN-91KB167NSAK, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1249
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. (Source: MSSQLServer, Error number: 3933)
Get help: http://help/3933

Thanks again for any continued help! It's really appreciated.
Post #1595912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse