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.
Read on for the answer.