SQL Server Replication Snapshot Errors

SQL Server Replication Snapshot ErrorsOriginally published on

I ran across interesting SQL Server replication snapshot errors recently that are worth sharing, so I wrote SQL Server Replication Snapshot Errors. It happened while using a distributor running SQL 2008, a publisher running SQL 2005, and the published database set to 2000 (80) compatibility. When adding a new subscription (version and compatibility of subscriber are irrelevant) the snapshot agent failed with the following error (extra details omitted for readability):

Error messages: 
Source: Microsoft.SqlServer.Smo 

Message: Script failed for Table 'dbo.Template_HeaderFooter'. 

Message: Column HeaderFooter_Value in object Template_HeaderFooter contains type NVarCharMax, which is not supported in the target server version, SQL Server 2000. 

The distributor was recently upgraded from SQL 2005 where this wasn’t a problem. A quick search of Microsoft’s KB turned up nothing on the error. After some tinkering I was able to figure out a workaround: change the compatibility level of the published DB to 2005 (90). While this works, it’s less than ideal if your DB is already live because you may break code by changing the compatibility level.

Unfortunately I haven’t found any other workarounds to the problem so if this is happening to you your best bet is to pick a time when no one is using the DB, change the compatibility level, take your snapshot, then change the compatibility level back. Of course an even better strategy is to work with your development teams to get the DB moved up to 2005 compatibility permanently.


About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.

<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>


About UpSearch


UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.