none
Transaction replication - create subscriber error RRS feed

  • Question

  • Hi I am configuring Transaction replication 

    source: SQLserver 2014 

    Dest :SQL Server 2016 Cu7

    I created publication, While create subscriber, I am getting following error. Any expert idea would be highly appreciated 

    Command executed and error occurred 

    exec sp_addpullsubscription_agent @publisher = N'SQLDB1SER',
     @publisher_db = N'HRDB', 
     @publication = N'DB_PUB', 
     @distributor = N'SQLDB1SER', 
     @distributor_security_mode = 1, 
     @distributor_login = N'', 
     @distributor_password = null, 
     @enabled_for_syncmgr = N'False', 
     @frequency_type = 64, 
     @frequency_interval = 0, 
     @frequency_relative_interval = 0, 
     @frequency_recurrence_factor = 0, 
     @frequency_subday = 0, 
     @frequency_subday_interval = 0, 
     @active_start_time_of_day = 0, 
     @active_end_time_of_day = 235959, 
     @active_start_date = 20191016, 
     @active_end_date = 99991231,
      @alt_snapshot_folder = N'', 
      @working_directory = N'', 
      @use_ftp = N'False', 
      @job_login = N'PROD\REP_user',
       @job_password = 'abcd', 
       @publication_type = 0
    GO

    +++++++++++++++++++++++++++++++++++

    I found the same error as bellow which some one got

    https://dba.stackexchange.com/questions/235523/replicaton-the-current-transaction-cannot-be-committed-and-cannot-be-rolled-ba/235524

    +++++++++++++++++++++++++++++++++++++++

     



    • Edited by ashwan Wednesday, October 16, 2019 11:53 AM
    Wednesday, October 16, 2019 11:47 AM

Answers

  • Hi Cathy Thank you for the reply. I run this script with uses with sysadmin privileges and was failed.  what I did was  this script run using "SA" account . and was successfully created.  Actually I dont know why . Same thing I did other env with sysadmin user and worked . but didnt work on prod. so now I able to create with "SA" sql account on the server . Thank you 
    • Marked as answer by ashwan Thursday, October 17, 2019 8:46 AM
    Thursday, October 17, 2019 8:46 AM

All replies

  •  

    Hi ashwan,

    Generally this issue might be caused by that your distribute agent account or subscribe agent account has not enough permission. From your script, it seem your agent job account is an domain account, is it right? if I misunderstood, please let me know.  Please check your agent job account permission. Try to set distribute agent account to SQL Server Agent service account.  And please check that  if  there is additional message in the SQL error log. 

    By the way, did you try to use SSMS UI to configure transaction replication?
    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, October 17, 2019 8:17 AM
  • Hi Cathy Thank you for the reply. I run this script with uses with sysadmin privileges and was failed.  what I did was  this script run using "SA" account . and was successfully created.  Actually I dont know why . Same thing I did other env with sysadmin user and worked . but didnt work on prod. so now I able to create with "SA" sql account on the server . Thank you 
    • Marked as answer by ashwan Thursday, October 17, 2019 8:46 AM
    Thursday, October 17, 2019 8:46 AM