2

After creating a replication publication, I go ahead and create a subscription using the script below:

-----------------BEGIN: Script to be run at Publisher 'MY_PUBLISHER_SERVER'--------------
use [MY_PUBLICATION_DB]
exec sp_addsubscription 
 @publication = N'MY_PUBLICATION', 
 @subscriber = N'MY_SUBSCRIBER_SERVER', 
 @destination_db = N'ORCA_Repl_Sub', 
 @subscription_type = N'Push', 
 @sync_type = N'automatic', 
 @article = N'all', 
 @update_mode = N'read only', 
 @subscriber_type = 0



exec sp_addpushsubscription_agent 
 @publication = N'MY_PUBLICATION', 
 @subscriber = N'MY_SUBSCRIBER_SERVER', 
 @subscriber_db = N'ORCA_Repl_Sub', 
 @job_login = N'MY_COMPANY\sqlrpl', 
 @job_password = N'MYP455w0rd',
 @subscriber_security_mode = 1, 
 @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 = 20190423, 
 @active_end_date = 99991231, 
 @enabled_for_syncmgr = N'False', 
 @dts_package_location = N'Distributor'
GO
--------------END: Script to be run at Publisher 'MY_PUBLISHER_SERVER'-----------------

to my surprise I get all the following errors when running the second procedure:

Msg 50000, Level 16, State 1, Procedure msdb.dbo.sp_add_jobstep_internal, Line 255 [Batch Start Line 14]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sys.sp_MSadd_repl_job_unsafe, Line 376 [Batch Start Line 14]
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

enter image description here

Basically Replication Scripts Stopped Working..

How could be a solution for this? where to find more information about these errors?

2

I re-connected to sql-server using the credentials of my replication agent - which is a sysadmin on my publication server, and db_owner on my subscription database at my subscription server.

1 - right click on the ssms icon

enter image description here

2 - on the SSMS option above, press shift + right click and choose the Run as a different user

enter image description here

3 - add the credentials of the different user - in my case it was my replication agent account

and it all worked fine after that, as you can see on the picture below:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.