October 9, 2014 at 9:11 am
Using MSSQL Server 2012 Standard on a Windows 2008R2 Standard server.
For the tablediff utility MSDN says:
The Transact-SQL script generated to bring the destination table into convergence does not include the following data types: varchar(max), nvarchar(max), timestamp...etc
We have a small replication job running and one of the tables contains a timestamp field. After running tablediff the generated script contains a note:
-- Column(s) AccessTS are not included in this script because they are of type(s) text, ntext, varchar(max), nvarchar(max), varbinary(max), image, timestamp, or xml. Columns of these types cannot be updated by tablediff utility scripts; .....
The updated record is in the script file, but the timestamp field is omitted.
All good so far.
However, another table contains nvarchar(max) fields. The scripts that are generated for these tables do not contain the above message and instead contain these nvarchar(max) fields in both the update and insert into statements!
So why does it say it will ignore these types of fields, but then actually include the nvarchar(max) ones in the generated scripts?
Can anyone offer an explanation? Or perhaps just replicate the issue? Or maybe I just misread the help file?
Thanks
Darren
October 9, 2014 at 11:45 am
Just a guess, but I'd bet the NVARCHAR(MAX) columns that are being included are small enough to be stored in-row so tablediff is considering it as NVARCHAR(N).
I did a test and can duplicate that the tablediff utility is not ignoring varchar(MAX) columns when generating the convergence script. I even ran\:
sp_tableoption N'table', 'large value types out of row', 'ON'
to force the data to be stored as LOB_DATA.
It could be because it is still not large enough to ignore?
My test was to create varchar(max) column on a couple of tables and update some rows to have < 8000 bytes/characters and some to have > 8000 bytes/characters. Then I ran tablediff against another copy of the table. Whether stored as IN_ROW_DATA or LOB_DATA the script generated included the varchar(max) column.
Could be that the BOL article is no longer correct or there are some exceptions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 10, 2014 at 6:11 am
Thanks for looking at this and the reply.
Nice to know I'm not going mad! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply