Ultra-bizarre characters hidden in SQL Server fields the resolution process _mssql

Article Author: jhkdiy
Release Date: 2009-10-11
Program structure: b/S
Development environment: Windows XP + IIS6 + VS2003
Database: SQL Server 2000
Part of the functionality: Export data from SQL Server to Access.
This system has done for a large six months, this import export or problem constantly, I am responsible for this import export is sandwiched in the net
Between the version and the stand-alone system, as long as any side of the database made changes to the import export will fail. Hey, upset.
More than that, and recently encountered a very strange problem.
Because the previous import export was done using the OPENDATASOURCE () function supported by SQL Server, and when encountering
64-bit systems will encounter unsupported microsoft.jet.oledb.4.0, so recoding, the technical step is to read data from SQL Server to the DataTable, and then traverse this DataTable, for each row, Regroup the data in an INSERT statement, then open the Access file link, execute the INSERT statement that you just generated, and insert the data into Access.
The statements I finally submit to access are spelled out according to the data in SQL Server, such as SQL Server:select A, B, C from tbltemp
The statements that are submitted to access execute are:

Copy Code code as follows:

STR1 = "Insert into"
STR2 = "Tbltemp (a,b,c) VALUES ("
strresult = str1 & str2 & "' AA ', 2, ' cc '" & ")"

The big problem with this is that there are special characters in the B field of the SQL Server table that make my program unable to spell out the complete string, the string strresult sometimes without the Last ""), but strangely, there are more than 40,000 records in this table, This behavior can only occur if you combine individual records. But submitting to access does not pass, prompting for SQL syntax errors.
I then queried this record of the database, Query Analyzer found no special characters, asked a colleague to know, he said before some of the table some characters saved the customer entered the return line, I immediately dawu, yes, carriage return line break is invisible ah, so, Want to use the following statement to query whether a carriage return line character is in the field:

Copy Code code as follows:

Select Charindex (char), DEMC from tblgc_jc_de where xh = 15641
Select Charindex (char, DEMC) from tblgc_jc_de where xh = 15641
Select CHARINDEX (char) + char (DEMC) from tblgc_jc_de where xh = 15641
Select Charindex (char () + char (), DEMC) from tblgc_jc_de where xh = 15641

But strangely enough, the return is 0, which is not found. This is depressing, the field in the end what characters are stored ah? , another colleague taught a trick to return all of the row records of the table directly in Enterprise Manager, and then select all of the fields to see that there is actually one more character:

What character is not a carriage return line break? In the Baisibuxie, suddenly remembered, rather look at this table physical data, must be able to find out what characters are stored in the field. But the table has 44,022 records, check a record of the physical data is in a haystack, how to do???
I just want to check the physical data for this record. Can you put this data into another table, which is the only record in this table, so you can't view it. Oh, this is simple, in fact, I also created a temporary database, the database has only one table, this table has only one record, which contains the above problematic records, the use of the SQL statement is as follows:
--TYZW is the official library.
Use TYZW
--Create a staging database, and then the problematic one
--The record is inserted into the temporary library.
Create DATABASE Tmptyzw
Go
SELECT * Into TMPTYZW. Tblgc_jc_de from tblgc_jc_de where xh = 15641
Go
Now it's time to look at the physical data for this record, first find out where the table is in the physical file in the sysindexes system table, and then we can view the physical data through the DBCC PAGE command:

Then use the DBCC PAGE command to view the physical data:

Now it's time to look at the physical data for this record:

God, unexpectedly is 0, really do not know how to deposit in. The problem finally knows where, but how to solve it? The easiest way to do this is to replace the 0, and then use the following statement to test:

Select Replace (DEMC, char (0), ') as DEMC from Tblgc_jc_de

But no, because the Replace function cannot find the 0 character, because it looks for two bytes, so the character cannot be found directly on the SQL Server and cannot be replaced. However, I thought again, can I use binary to find and replace? Looking at the data, use the following SQL statement to find out where 0 is in the field:

Check is able to find out, but I found that the Replace function is still not available, unless it is to replace 4-bit, that is, 0x0038.

Finally really helpless, can only directly have this special character of the lot to intercept a text, that is, even that 38 do not have:

Select

Case when CHARINDEX (CONVERT (varbinary (1), 0x00), convert (varbinary, DEMC)) > 0

Then substring (Demc,1,len (DEMC)-1)

else DEMC

End as Demc

From Tblgc_jc_de

This is the final solution I'm using, because I've queried that only 8 of the 40,000-odd records have this 0 in the field. So even if you cut a text and affect the function and display of the program.

Although I now write a lot of steps seems to go very smoothly, in fact, this problem I spent a few days, mainly to find the problem is spent a lot of time. Now give this article hope that the majority of friends in the search for these problems in a number of directions, because the special characters stored in the field is not necessarily the regular carriage return line break, there may be other characters, in this we learn from each other to improve it.