Peer-to-peer publications do not support replicating timestamp columns as varbinary(8)

I came across this issue while helping a DBA through MSDN forums. The issue was that while trying to setup Peer-to-Peer replication he was getting an error. He was getting the below error, after creating the publication and when trying to set the property “Allow peer-to-peer subscriptions” to true.

“Peer-to-peer publications do not support replicating timestamp columns as varbinary(8). You cannot add an article with this option, nor add or alter a table to include a timestamp column as varbinary(8).”

The error is pretty much self explanatory that we cannot replicate timestamp columns  as varbinary(8) in Peer-to-Peer replication.

We have an option in Transactional Replication, to set options for articles in a publication.

In this case, there was this option “Convert TIMESTAMP to BINARY”. This option was set to true.

Now the resolution is to set the option to false.

There were two challenges that I faced while trying to find a resolution for this issue.

Challenge #1 – There was around 300 + articles  with timestamp columns. To check the properties of all the 300 + articles is not an easy task. What I did was to take the script of the publication, and then search for the different @schema_option values. Then I used this wonderful script for decrypting the different @schema_option values. You can see the script in this Blog .

I found that there was this one article for which the particular bit 0x08 was not set. 0x08 means Replicate timestamp columns. If  not set, timestamp columns are replicated as binary.

All I had to do is to lookup the script for the corresponding article and change the property to False.

Challenge #2 – The timestamp column which was replicated was a primary key. Every time, I set the property to False and try to set the “Allow peer-to-peer subscriptions” to true , I get the same error. And every time I noticed that the property was set back to True. I assume it is because if it allowed replication of timestamp column which is also a primary key, then in the subscriber you generally have a different value for the same row.

I tried changing the primary key constraint to a different column and I was able to change the property to false and set the “Allow peer-to-peer subscriptions” to true.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s