How to solve super-strange characters hidden in SQLServer Fields

Author: jhkdiy
Published on:
Program architecture: B/S
Development Environment: Windows XP + IIS6 + VS2003
Data Base: SQL Server 2000
Some functions: export data from SQL Server to Access.
This system has been used for more than half a year, and the import and export problems continue. I am responsible for this import and export, which is stored on the Internet.
Between the network version and the standalone version, the import and export will fail as long as any side changes the database. Hi, thank you!
More than that, and recently encountered a very strange problem.
Since the OpenDataSource () function supported by SQL Server is used for import and export
The 64-bit system does not support Microsoft. jet. OLEDB.4.0, so re-encoding. The technical step is to first read data from SQL Server to the DataTable, then traverse the DataTable, and recombine the data in each row into an Insert statement, then open the Access File Link, execute the Insert statement just generated, and Insert the data to Access.
The statements I finally submitted to Access are spelled out based on the data in SQL Server, such as SQL Server: select a, B, c from tblTemp.
The statement submitted to Access is:
Copy codeThe Code is as follows:
Str1 = "Insert"
Str2 = "tblTemp (a, B, c) values ("
StrResult = str1 & str2 & "'A', 2, 'cc'" & ")"

This is a major problem because there are special characters in the B Field of the SQL Server table. This character makes it impossible for my program to spell out the complete string. The strResult character is sometimes not followed by the final ") ", but it is strange that this table has a total of more than 40 thousand records, which only happens when you combine individual records. However, the SQL syntax error is prompted when the request is submitted to Access for execution.
I used the query analyzer to query this record of the database and found no special characters. I only knew it after I asked my colleagues, he said that some characters in some tables have previously saved the customer's input line breaks. I suddenly realized that, right, the line breaks cannot be seen. So, use the following statement to check whether the field contains a carriage return line break:
Copy codeThe Code is as follows:
Select charindex (char (10), demc) from tblgc_jc_de where xh = 15641
Select charindex (char (13), demc) from tblgc_jc_de where xh = 15641
Select charindex (char (10) + char (13), demc) from tblgc_jc_de where xh = 15641
Select charindex (char (13) + char (10), demc) from tblgc_jc_de where xh = 15641

But the strange thing is that the return value is 0, that is, it cannot be found. This is depressing. What characters are stored in the field ?, Another colleague taught me how to directly return all Row Records of the table in the Enterprise Manager, and then select all the rows to view the field. It is found that there is indeed an extra character:

What are the characters of the carriage return or line break? When I was puzzled, I suddenly remembered that it would be better to check the physical data of the table to find out what characters are stored in the field. However, this table has 44022 records, and the physical data of a record is a haystack. What should I do ???
I just want to query the physical data of this record. What should I do? Can you put this data in another table? This table only has this record. Can you check it. Oh, this is simple. In fact, I have created a temporary database with only one table. This table has only one record, that is, the problematic record above, the SQL statement used is as follows:
-- TYZW is the official database
Use TYZW
-- Create a temporary database and the problematic one
-- Insert records into the temporary database.
Create database tmpTYZW
Go
Select * into tmpTYZW .. tblgc_jc_de from tblgc_jc_de where xh = 15641
Go
Now it is time to check the physical data of this record. First, we need to find the location of the table in the physical file in the sysindexes system table. Then we can run the dbcc page command to view the physical data:

Run the dbcc page command to view the physical data:

Now it is time to look at the physical data of this record:

 

Oh, my God, it turned out to be 0. I really don't know how to store it. The problem finally exists, but how can we solve it? The simplest way is to replace this 0, so you can 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 is found in two bytes, so the character cannot be found directly on SQL Server, cannot be replaced. However, I thought again, can I use binary to find and replace it? After reading the information, you can use the following SQL statement to find the position of 0 in this field:

I can check it out, but I found that the replace function still cannot be used, unless it is replaced by four digits, that is, 0x0038.

Finally, I am helpless. I can only cut off a text section with this special character, that is, I don't even need that 38:

Select

Case when charindex (convert (varbinary (1), 0x00), convert (varbinary (200), demc)> 0

Then substring (demc, 1, len (demc)-1)

Else demc

End as DEMC

From tblgc_jc_de

 

This is the final solution I used now, because I have queried it, only 8 of the more than 40 thousand records have this 0 in the field. Therefore, even if a text is truncated and the function and display of the program are affected.

Although many of the steps I have written seem to be smooth, it takes me a few days to find the problem, which takes a lot of time. Now, I hope that most of my friends can find these problems in multiple directions, because the special characters stored in the field are not necessarily regular line breaks or other characters, let's learn from each other and improve it.