Post reply

Snapshot agent failing: Cannot promote the transaction to a distributed transaction

  • July 1, 2013 at 6:07 am

    #295217

    Hi,

    I'm receiving the next error when i create my publication snapshot (my publication only have a table/article):

    "

    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 = Publication_Tables

    @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 MYSERVER, 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

    "

    I check and there aren't any open tran. I don't know what is the problem.

    Anyone could say me how solve the issue?

    Thanks.

  • July 2, 2013 at 2:22 am

    #1628937

    I fix this issue executing turning off the 'remote proc trans' server option.

    My question is: Can i create snapshot (transactional replication) when ''remote proc trans' server option is on?

    Many thanks in advance.

  • July 2, 2013 at 3:19 am

    #1628958

    Is there a reason you need that option? It is a deprecated feature and has no affect in SQL Server 2012.

    Joie Andrew
    "Since 1982"

  • July 2, 2013 at 3:28 am

    #1628960

    Hi Joie.

    Actually there are old aplications that are using this option. As you say, i know that in SQL 2012 this option is removed. But in SQL Server 2008 (our environment) can i define transactional replication with this option activated? what would be the options i have (for example i have to say to developer to rewrite distributed transaction)?

    Many thanks.

  • July 2, 2013 at 3:41 am

    #1628963

    Looking around on the web it looks like you cannot setup transactional replication with that option enabled. It has been noted on several TechNet and MSDN forum sites as well as here in SSC, but nothing in BOL or MS support stating that explicitly.

    As for an alternative for apps that use remote proc trans BOL recommends using linked servers instead.

    remote proc trans Option

    Joie Andrew
    "Since 1982"

  • July 2, 2013 at 3:48 am

    #1628964

    Thanks Joie.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply