Error converting ntext to text

Nazca_Aaron

Background info: I am using DC 5.1.0.106. Database is SQL 2000.

I have a table in a database that has a text field, which basically holds XML text. When I do a Data Compare and try to execute the resulting SQL script, I get the error:

Msg 518, Level 16, State 1, Line 3
Cannot convert data type ntext to text.
The statement has been terminated.

I poked around and found a MS bug article that talked about the above error, saying "If you attempt to use an UPDATETEXT statement to convert TEXT to NTEXT (the UNICODE equivalent to TEXT) or vise versa, you will receive the following error: " , followed by my error. I am not sure that I can take out the UPDATETEXT statement in the SQL generated by DC.

If I run the same compare with DC 4.0.0.110 and execute the resulting SQL script, I do not get any errors.

Is there a work around for this? There is a suggestion in the MS article, but I'd rather have the workaround scripted with DC instead of editing the script by hand.

Otherwise, I am finding DC (and SQL Compare) to be an invaluable tool

Nazca_Aaron

Oops, forgot to include the link to the MS article. Check it out here.

Nazca_Aaron

A correction (since I can't edit the original post):

In DC 5, the compare results in 3 tables that do not compare properly, and I need to manually set the key to get them to compare, after which the compare works fine (until I run the SQL script, that is).

In DC 4, these 3 tables do not even show up in the compare, and there is no option that I know of to manually set the key, as in DC 5. So, the compare with DC 4 works because it doesn't script the problem table at all, and thus the SQL script has no errors.

My assertion that the script works under DC4 but not DC5 is technically correct, but very misleading, so please disregard.

Brian Donahue

Hello,

The column is of the type ntext in the dirst database table, and text in the second? Are there any Unicode (double-byte) characters in the script?

Nazca_Aaron

The columns in both databases are the same; I used SQL Compare to script the table creation. The text is basically an XML document, and there are no special characters in the suspect statements.

The SQL statements that fail look something like:

EXEC(N'DECLARE @pv binary(16)
'+N'SELECT @pv=TEXTPTR([XmlDocument]) FROM [dbo].[XchangeStandard] WHERE [XchangeStandardId]=9
UPDATETEXT [dbo].[XchangeStandard].[XmlDocument] @pv NULL NULL N''

whereas statements updating the same table that appear to work are basic INSERT INTO statements (i.e.

INSERT INTO [dbo].[XchangeStandard] ([XchangeStandardId], [Version], [XmlDocument], [Type]) VALUES (9, ...

It seems as if a single record is being split into two different statements for some reason: an INSERT INTO and an EXEC('INSERT INTO...). Hopefully that makes at least a little sense? :?

Brian Donahue

Hello,

We think this could be a bug in the software to do with bounds checking that would cause SQL Compare to improperly use UPDATETEXT if the length of the column is exactly a multiple of 4000.

Can you please retry the compare and synchronize using the 'trim trailing spaces' option?

Thanks!

Nazca_Aaron

I set the compare to trim trailing spaces. However, the same error remains.

There are 9 records in the table I am attempting to script. The first 8 records do not use UPDATETEXT in the script, and they run just fine. However, the last record has a text field that is significantly longer than the others (i.e. there is more actual text in the field). This record is the one causing the script to fail.

As it is a text field, I cannot do a LEN(field) to find the length. If I convert the column to a VARCHAR(8000), the length returns 8000. I suspect the text field is actually longer than that, but max VARCHAR length is 8000.

Thanks for all your help, Brian.

Brian Donahue

Hi,

Can't think of anything else. Can you please send backups of the databases to support@red-gate.com?

Thanks!

Brian Donahue

This issue should be fixed in SQL Data Compare 5.2. The problem was the one described, about choosing the breakpoint in the data for the UPDATETEXT function.