SQL Server Performance Forum – Threads Archive

Search for Non-Printable character

I have a DTS package which imports a text file into a table. It works properly. Yesterday, it imported part of the text file. I have checked everything and now, I suspect to non printable character in the text file. How can I check my text file to find out such a character? Do you think the reason is something else than non printable character? CanadaDBA

---

Did the DTS report that it completed successfully?
If the DTS is run by a job, also check the job history steps DECLARE @a varchar(255)
DECLARE @i int SELECT @i = 1
SELECT @a = ”
WHILE (@i <= 255)
BEGIN
SELECT @a = @a + CHAR(@i), @i = @i + 1
END SELECT LEN(@a)
SELECT ‘[‘ + @a + ‘]’ —-
———–
255 (1 row(s) affected)
—————————————————————————————————————————————————————————————————————————————————————–
[ !"#$%&'()*+,-./0123456789:;<=>[email protected][]^_`abcdefghijklmnopqrstuvwxyz{|}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘#%92“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ] (1 row(s) affected) As you can see, a varchar is able to hold all possible characters.
If the textfile contained a 0 byte then LEN() would still report the correct length, but when outputting the string it would be truncated just before the 0 byte. A quick search in ultraedit would determine if the textfile contained a 0 byte but Id take a gues that the problem lies elsewhere

---

Thanks Chappy, I downloaded the UE and will try to find out if there is any 0 length character. CanadaDBA

---

Yes, UltraEdit solved my problem. It’s great text editor. Thanks, CanadaDBA

---

]]>