November 2, 2010 at 11:30 am
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)
November 2, 2010 at 11:35 am
Both server and database level collation same as well?
November 2, 2010 at 11:58 am
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 :
'<AddressBlock>XXXXXX 6103 C/O CODE ENFORCEMENT DIVISION 38250 XXXXXXXXXXXXXX PALMDALE, CA 93550</AddressBlock>'
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.
November 2, 2010 at 11:56 pm
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