Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Issues replicating XML data types for databases in SQL Server 2000 compatibility mode


By:   |   Last Updated: 2013-04-26   |   Comments (3)   |   Related Tips: More > Replication

Problem

We have a legacy application that was not certified for SQL 2005, so we were running it on a SQL 2005 server using SQL 2000 compatibility mode. The in house development team is increasing functionality by writing custom code around the existing tables and have created some tables of their own. One thing they missed was the SQL 2000 compatibility mode and they have created tables with XML data types, which is allowed in SQL 2005 on databases running in 2000 compatibility mode. The compatibility mode is for syntax and not features which I can understand why this was allowed. The downside is this table will not replicate, it fails trying to generate the snapshot with the following error "Script failed for Table dbo.TableName".

Solution

Looking into replication monitor I was able to find a little more detail and I saw this line which clued me into what the problem was:

"Message: Either the object or one of its properties is not supported on the target server version".

With that said, I was assuming replication was not able to support this configuration of SQL 2005 with databases running in SQL 2000 compatibility mode. After looking into the situation deeper, I found a property of the article I could change to get this to work. It was called "Convert XML to NTEXT" and while I am mentioning this there is also a property called "Convert MAX data types to NTEXT" that would also resolve issues with VARCHAR(MAX) and VARBINARY(MAX). These default to false as seen below.

Default Article Properties

After changing to TRUE I was able to generate and apply the snapshot without error:

Modified Article Properties

These article properties are accessible by going to "Properties" of the publication -> Articles --> Show Properties of highlighted article through SSMS:

Publication Properties

Now the snapshot generation process is successful as shown below:

Snapshot Generation

One thing to note, the publisher has XML as the data type that was originally defined for the table, but the subscriber has NTEXT:

Publisher Table

Subscriber Table

Here is a copy of the script file that the snapshot agent generated so you can see the data type in the script:

drop Table [dbo].[TestXML]
go
SET ANSI_PADDING OFF
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestXML](
 [ID] [int] NOT NULL,
 [XMLDataType] [ntext] NULL
)
GO
SET ANSI_NULLS ON
go

In summary when mixing replication and SQL Server versions, be aware of data types that are supported/unsupported and look into your replication agent properties to see if there are options to overcome these errors.

Next Steps


Last Updated: 2013-04-26


get scripts

next tip button



About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 01, 2013 - 12:08:59 PM - Chad Churchwell Back To Top

Both the publisher and subscriber were SQL Server 2005 with the publisher in SQL 2000 compatibility mode and the subscriber is 2005 compatibility mode if I remember correctly


Monday, July 01, 2013 - 9:37:46 AM - Ani Back To Top

Good article. What was the server version and database compatibility mode on the subscriber?


Monday, May 06, 2013 - 2:01:47 PM - Anand Back To Top

Good one Thanks!!


Learn more about SQL Server tools