BCP Export Automatically Converts Empty Strings to NUL

Question

All, in this post xp_cmdshell Query Length Too Large I managed to obtain a fix for a problem I was having with the query size limit using xp_cmdshell. The reason I was writing such a long query was to attempt to get around another problem, the fact that bcp seems to convert empty strings (from a table containing fields with empty strings) into some encoded character ('NUL' or 'r', or something). This is effecting what I can use my exported data for. The export query I have is

string strBcp = String.Format(
    "declare @sql varchar(8000) " +
    "select @sql = 'bcp \"SELECT * FROM [{0}]..[ivwTmpDrgDataView];\" ' +" +
    "'queryout \"{1}\\DRGData.txt\" -c -t -T -S ' + @@servername " +
    "exec master..xp_cmdshell @sql;",
    strDatabase,
    strDataDir);

This works fine, but in the exported file database values are converted

NULL -> Empty String (Which is fine!)
Empty String -> Some strange character

I could read the .txt file with C# and then recode - what is the best way to convert my 'strange character' back to what it should be, an empty string?

Thanks for your time.

Answer 1

This is by design, if bcp converted both null and empty strings to the same output (let's say an empty string), then there would be no way to disambiguate them later.