Re: Unable to Load Data into Database

For Issue 2, you should refer to
http://www.joesack.com/RemoveCarriageReturns.htm. This article contains the following:

Scenario:

You have a data export process from SQL Server to an outside file. The recipient complains because the text you are sending contains carriage returns (this could also be any kind of character they think may be invalid or problematic).

Solution:

If using a varchar, nvarchar, char, or nchar column, Use the UPDATE statement coupled with the REPLACE and CHAR functions. If using a text or ntext column, use UPDATETEXT.

How to do it:

For varchar, nvarchar, char, and nchar columns - you can remove the invalid characters using a combination of functions.

The CHAR function is used to convert an integer value into an ASCII character. The Carriage return character is represented by CHAR(13), line feeds are represented by CHAR(10), and Tabs are represented by CHAR(9). The REPLACE function is used to search for a specific character or string within an expression.

In the below UPDATE statement, I am removing all carriage returns from a varchar field and replacing them with a blank space:

UPDATE Test
SET BlurbVarchar = REPLACE(BlurbVarChar, CHAR(13),' ')

If you do not want to modify the source data, you can instead use a query that performs the same function using a SELECT statement:

SELECT REPLACE(BlurbVarChar, CHAR(13),' ') FROM Test

Updating a text file is a little trickier. If you are using Transact-SQL extensions to update text fields, you may be familiar with UPDATETEXT. This function will allow us to remove the carriage return from the text field. The method is slower than REPLACE, because we have to update one row at a time (and sometimes one row multiple times for each occurrance of the carriage return).

The example below updates the text field "BlurbText" by removing any carriage returns. I use a WHILE loop to iterate through each row and remove the data. You can also use a Cursor if you prefer.

The _at_PTR variable is used to contain the text pointer, which is retrieved by using the TEXTPTR function. The _at_POS variable is used to identify the location of the carriage return (using CHAR(13)). I use TOP 1 keyword to retrieve the first row that has a carriage return in it. It doesn't matter which one, only that I retrieve a specific row that needs correcting. Lastly, I run UPDATETEXT and then loop around all over again, until there are no more rows with a carriage return.

• Keep doing this until all carriage returns are gone WHILE EXISTS (select * from Test where charindex(char(13),blurbtext)>0) BEGIN DECLARE _at_PTR binary(16) DECLARE _at_POS int
• Grab a single row and the position of the carriage return SELECT TOP 1 _at_PTR = TEXTPTR(blurbtext), _at_POS = charindex(CHAR(13), blurbtext)-1 FROM Test WHERE BlurbText LIKE '%' + CHAR(13) +'%'
• Replace the carriage return with a blank UPDATETEXT Test.BlurbText _at_PTR @POS 1 ''

END Received on Fri Dec 17 2004 - 21:47:36 CET