none
MS SQL Server 2017 - Error following official tutorial - creating new publication while setting up transactional replication

    Frage

  • Hi, I am trying to set up replication using SQL Server 2017 Developer edition. The publisher, distributor, and subscriber are all local. I have been (faithfully) following the tutorial here: 

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-2017

    In the section "Configure the publisher for transactional replication" after completing step 13 and clicking "Finish",which should create the initial snapshot for replication, the New Publication Wizard reports there was an Error Creating Publication 'AdvWorksProductTrans'. The message is clickable and launches a dialog that reads as follows:

    --------------------------------------------------------------------------------------------------------------------------------------------

    SQL Server could not create publication 'AdvWorksProductTrans'.

    Additional information:

    -> An exception occurred while executing a Transact-SQL statement or batch.

        (Microsoft.SqlServer.ConnectionInfo)

        -> The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

             The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

             Changed database context to 'AdventureWorks2012'. (Microsoft SQL Server, Error: 50000)

    --------------------------------------------------------------------------------------------------------------------------------------------

    Any help will be greatly appreciated. Thanks!



    Dienstag, 3. Juli 2018 19:07

Alle Antworten

  • You need to have sysadmin rights on the local server and have SQL Agent running.

    Dienstag, 3. Juli 2018 20:10
  • Something is messed up here.

    Let's try this in your master database.

    exec sp_replicationdboption 'AdventureWorks2012', publish, true
    GO
    use 'AdventureWorks2012',
    GO
    exec sp_addpublication 'AdvWorksProductTran', @status='active'
    GO

    Let us know if you get any error messages. 


    Mittwoch, 4. Juli 2018 13:13
    Moderator