Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Distribution Synchronisation Error


SQL Server Distribution Synchronisation Error

Author
Message
Kitty Katie
Kitty Katie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 33
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!
Matt Crowley
Matt Crowley
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
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.
Kitty Katie
Kitty Katie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 33
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!


Matt Crowley
Matt Crowley
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
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.
Kitty Katie
Kitty Katie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 33
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search