Post reply

Unwanted NULL Characters in Query-to-TextFile Output: A Solution

  • October 23, 2008 at 7:57 am

    #194888

    This may be old news to everyone, but I posted a question here (http://www.sqlservercentral.com/Forums/Topic581789-338-1.aspx) that went unanswered. I found what the problem is, and figured I'd share the answer.

    SSMS defaults to saving query output to text files in UNICODE format, which inserts a HEX(FF) and HEX(FE) at the beginning of the file, plus an additional HEX(00) - NULL - byte after each character in the output. This was trashing byte-wise importation programs that were using my generated text files as input.

    The solution I found is to tell SSMS to save using ANSI, not UNICODE, encoding, as outlined here:

    http://tinyurl.com/5ksees

    The annoying thing is that there seems to be no way to configure this as a default in SSMS.

    Maybe this will spare some other SSMS novice hours of researching this problem!

    HTH,

    Rich Mechaber

  • July 26, 2010 at 1:43 am

    #1198051

    Hi,,

    Are you still there?

    I have the same problem now and cannot find solution anywhere. But my problem is I have to use bCP and while BCP it puts the same as you said HEX 00 after every field and I do not know what to do? Can you help me

    Thanks!

  • July 26, 2010 at 6:19 am

    #1198147

    Hey, I don't use bcp, but I found this link for you:

    http://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/[/url] and maybe it will help:

    -n (native format): The bcp utility retains the database native data types when bulk copying the data to the data file. Microsoft recommends that you use this format to bulk copy data between instances of SQL Server. However, you should use this format option only when the data file should not support extended or double-byte character set (DBCS) characters.

    Maybe you can either use a format file or CAST your output as ANSI data?

    Good luck,

    Rich

  • July 26, 2010 at 6:28 am

    #1198152

    Hi,

    Thanks for reply!

    I read that article before thanks!...

    I am using format file, I did fix it but that's very strange. I did two things, one changed datatypes of fields from varchar to "CHAR" with fixed-widths which I need, then in format file used field terminator empty [""] and then while BCP I used codepage [-C1252]

    Then it did remove the all hex characters.

    Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply