Tuesday, February 28, 2017

SQL Replication on a Table That Contains a SQL_Variant Datatype

I recently tasked with setting up Transactional Replication in SQL 2008 R2. While this in and of itself isn't necessarily complicated, I did run into an issue that kept the initial snapshot from being created. One of the articles (tables) in the publication had two columns that were defined with a SQL_Variant type and the snapshot agent could not convert those columns to create the snapshot. I tried the various column convert settings in the article properties, but they did not help. Only after changing the snapshot format from Native SQL Server to Character was the snapshot able to be created. All of the other article properties were left as default. Since I want all the other articles in this publication to retain the Native SQL Server snapshot format, I created a specific publication for tables that contain SQL_Variant columns.

No comments:

Post a Comment