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.
After changing to TRUE I was able to generate and apply the snapshot without error:
These article properties are accessible by going to "Properties" of
the publication -> Articles --> Show Properties of highlighted
article through SSMS:
Now the snapshot generation process is successful as shown below:
One thing to note, the publisher has XML as the data type that was
originally defined for the table, but the subscriber has NTEXT:
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]goSET ANSI_PADDING OFFgoOTED_IDENTIFIER OSET ANSI_NULLS ON GO SET Q UN GO CREATE TABLE [dbo].[TestXML]([ntext] NULL ) GO SE[ID] [int] NOT NULL, [XMLDataType ]T ANSI_NULLS ONgo
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.
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment