Day 26: Replication Errors

Day 26: Replication Errors

Replication is one of my favorite topic, I worked on Transaction Replication, and Blogged about it here.

  • Replication is possible from higher version to lower version.
  • configure replication… remember to configure from higher version to lower version. means in your case configure using sql server 2008 management studio to Sql server 2005 or earlier.
  • Cannot make the changes in articles (ddl) when the replication is configured at publisher unless removing that article and making changes and re-configuring the article
  • Cannot changes an identity column for the publisher article unless removing changing and adding that article
  • Make sure that which database will have which commands running commands on wrong databases(publisher/distributor/subscriber) may gives you an error

Most of the replication related errors are due to not properly configuration/knowledge of the subject.

As replication is a tool which has its own way of working not following the process could cause an error. Sometimes removing everything and starting/ configuration from the beginning would be the best and easiest way to resolve.

I have written/documented  some blog/links about at my blog post

>> Adding/deleting articles on replication.

>> How to configure replication(step by step)

>> some troubleshooting

Here are some of the errors you get on Replication :

 

  1. 1.        Error when Adding subscription to an existing publisher:

Msg 20027, Level 11, State 1, Procedure sp_MSadd_subscription, Line 222

The article ‘(null)’ does not exist.

Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1186

Could not update the distribution database subscription table. The subscription status could not be changed.

Msg 14057, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 383

The subscription could not be created.

Resolution:

Once you have publisher setup you can configure the subscriber as:

—Adding and configuring subscriber.

exec sp_addsubscriber @subscriber = ‘SubscriberserverName’,,@description = ‘Publisher_to_Subsriber’

go
sp_addsubscription @publication = N’publisherDB’, @subscriber = @@servername,
@destination_db =’subsriberDB’, @sync_type = N’automatic’
GO

generally it’s a very simple process and should not give an error, could be here user might not be configured subscriber properly and missed some steps.

On the other hand it is very easy using GUI, to setup the subscriber.

Just goto publisher and create new subscriber(either create snapshot of subscriber or just restore the backup and “subscriber already having data” option and setup the subscriber.

  1. 2.        Access Issue:

Error: 15404

Could not obatain information about Windows NT group/user ‘……..’, error code 0x6e. (Microsoft SQL Server, Error: 15404)

If you do not have sysadmin access ”sa” privilege and to subscriber as well. If not you may get this error.

http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/52601866-23d6-4150-bcf5-dfcad24f43c3/

 

 

  1. 3.        Removing replication:

When you try to remove the replication you may get below error could be if you don’t follow the steps.

Msg 15247, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job [job name] was not removed because it has a non-standard name; manually remove the job when it is no longer in use.

or

Msg 22538, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 155

or

Msg 2560, Level 16, State 9, Procedure sp_MSremoveidrangesupport, Line 88
Parameter 1 is incorrect for this DBCC statement.
Msg 20025, Level 16, State 1, Procedure sp_dropmergepullsubscription, Line 339
The publication name must be unique. The specified publication name ‘MainSync’ has already been used.
Msg 14056, Level 16, State 1, Procedure sp_dropmergepullsubscription, Line 426
The subscription could not be dropped at this time.

Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line 27

Cannot drop the table ‘dbo.xxx’ because it is being used for replication.

https://thakurvinay.wordpress.com/2009/12/17/remove-replication/

 

  1. 4.        configuration:

 

Msg 21079, Level 16, State 1, Procedure sp_getpublisherlink, Line 52
The RPC security information for the Publisher is missing or invalid. Use sp_link_publication to specify it.
Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

If tried to configure replication from sql server 2005 management studio for replication between 2008 and 2005 you may get errors like this.

  1. 5.        Change article:

Msg 21080, Level 16, State 1, Procedure sp_MStran_altertable, Line 273
The ‘msrepl_tran_version’ column must be in the vertical partition of the article that is enabled for updatable subscriptions; it cannot be dropped.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75

Could not drop article. A subscription exists on it.

You cannot delete the columns configured for replication, means we cannot change the table structure when you configured the article for replication. You have to first delete the replication and then can change the structure.

I blog about adding and removing article in replication:

https://thakurvinay.wordpress.com/2010/01/27/adding-articles-to-and-dropping-articles-from-existing-publications/

 

  1. 6.        Run command on right database

Msg 18757, Level 16, State 1, Procedure sp_replshowcmds, Line 21

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

These command should be in “Distributor” database else it will give you an error.

Same is applicable for “sp_browsereplcmd” and other commands

And

If you run sp_addsubscription command at subscriber you may get below error:

Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 252

This issue might be caused by that you execute this statement in subscriber.

You should know where to run which command should run where.

 

  1. 7.        Cannot DDL statement

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_alterschemaonly, Line 45
The DDL statement cannot be performed at the Subscriber or Republisher.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.

Msg 3609, Level 16, State 2, Procedure usp_xxx_upd, Line 21
The transaction ended in the trigger. The batch has been aborted.

a. You cannot change it at the subscriber, irrespective of whether @replicate_ddl is set to 0 or 1

b. If @replicate_ddl is set to 1, then changing the proc on the publisher will result in the proc being changed at the subscriber next time it is synchronised (if a published article)

  1. 8.  Subsriber cannot insert/update BLOG using WriteTEXT or UpdateTEXT

Msg 20508, Level 11, State 1, Procedure sp_MSreplraiserror, Line 4

Updateable Subscriptions: The text/ntext/image values inserted at Subscriber will be NULL.

The reason is also explained in BOL: “Subscribers cannot update or insert text, ntext or image values because it is not possible to read from the inserted or deleted tables inside the replication change-tracking triggers. Similarly, Subscribers cannot update or insert text or image values using WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.”

 

  1. 9.        Handle Identity

handle the identity column in the replication. Should not make the changes and make identity column at publisher when replication is configured.

Cannot insert explicit value for identity column in table xxx’ when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

 

Advertisements
This entry was posted in Replication, Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s