bcp command outputting Chinese characters instead of table

Question

I have a batch file that imports a delimited text file into SQL Server using BCP, runs some processing using SQLCMD, and then outputs the processed data via BCP. It currently gives a string of Chinese characters instead of a delimited text table. The weird thing is that it was working two days ago and I haven't changed anything.

I've confirmed that the input BCP and SQL Server processing steps work; the SQL Server table that holds the data for output is correct. I also tried using the -C option to specify a codepage as suggested here, with no change.

Here's the BCP code in question. I've split the lines for readability, the actual code is on one line as normal. It's using union all to put the column names at the top of the file.

bcp 
"select
    'MRN'
    ,'column name 2'
    ,[rest of the column names]
union all 
select distinct 
    iif(r.MRN is not null, cast(r.MRN as varchar), '') as MRN 
    ,[rest of the columns]
from DATA_MANAGEMENT.dbo.Daily_inpat_out as d 
left join DATA_MANAGEMENT.dbo.Roster_all_match as r 
    on d.Subscriber_ID = r.SUB_ID 
where 
    r.MO_DLGTN_STA_DESC = 'DELEGATED'" 
queryout "L:\Data_Management\Import_data\Daily_inpat\inpat_out_%mydate%_%mytime%.txt"
-c -S [servername] -U [user] -P [password] -t "|"

Here's the first part of the Chinese character output, the full string is much longer:

前籎潇瑶偟潲牧浡䝼偒䥟籄䕍䉍剅也䵁籅䥂呒彈䅄䕔卼扵捳楲敢彲䑉呼彘䕓呔义彇䕄䍓

Edit to add:

As mentioned in the comments the problem occurs in Notepad but displays correctly in other applications (Word, Excel, Notepad++), so this is an encoding issue. I'm am still hoping to understand the cause of the issue and how to fix it.

Answer 1

I was experiencing the same issue (also had a union between the data sets) but was building up my SQL statements

In my case i had selected '' as SomeField for known columns where there would be no data, in the top table satisfy the union.

I changed to NULL as SomeField, and changed my isnull checks to ISNULL(field,char(32))

SET @SQL = @SQL +'ISNULL('+@Cols+ ',CHAR(32))' + ' as '+@Cols+','

the bcp output file was correct.

I suspect in the above case its the

iif(r.MRN is not null, cast(r.MRN as varchar), '') as MRN

that is causing the encoding issue, perhaps iif(r.MRN is not null, cast(r.MRN as varchar), CHAR(32)) as MRN would have fixed the issue.