What setting can possibly allow the value of a character in the XML in a col/table/server?

  • I have restored a database from one server to another. Restoring it is fine, but I am trying to copy the data from one table to another and there is an XML column that makes me wonder, why is it that in the other server, it allowed the value (char(167) specifically, but when exported out, becomes char(21) Negative acknowledgement), yet when i am copying it over, it is barfing with:

    Msg 9420, Level 16, State 1, Line 1

    XML parsing: line 1, character 57, illegal xml character

    Is it only a setting? Or is there just a possibility that there was a fluke at the time that allowed the entry of these records to the table with XML column? It made it to the table in the other server (Production Server).

    That character in the XML file was not escaped, and yet it made it to the XML column on the table. Is there a table setting or column setting that would allow this?

    Thoughts, ideas? Please?

    Both servers are SQL2005 (Prod at 32 bit, Test Server at 64-bit)

  • Both server and database level collation same as well?

  • Yes, they are the same collation.

    Source Server Collation: SQL_Latin1_General_CP1_CI_AS

    Source DB Collation : SQL_Latin1_General_CP1_CI_AS

    Target Server Collation: SQL_Latin1_General_CP1_CI_AS

    Target DB Collation : SQL_Latin1_General_CP1_CI_AS

    Sample Data that exists on the XML column from Source :


    That inverted T in between the fist XXXXX and 6103 character is the invalid character. It is actually a char(167) from the source db/table.

  • Apparently, it's not really that the other server allowed the character and the test server did not allow it. The problem was during the export (BCP out). The export changes it to char(21) NAK. And when that data gets imported, that's when it barfs about the illegal character. If BCP was able to retain that char(167), should've been no problem.

    It's just finding that very few records among the millions of records. 🙁 But at least there is a work-around to inserting back those records, but it's an INSERT TO, not BCP or bulk insert.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply