question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked Criszhan-msft answered

Character getting changed from , to ?

Hi.
I am using BCP utility to transfer data from SQL table to a delimited file.

Below is the example of the query used:

 BCP "SELECT 1 as First, 
 quotename(COUNTRY,'""') as COUNTRY, STATUS, QUOTENAME('¸','""') as last  FROM TableName " queryout "path of file" -T -S servername -d DBInstance -b 500 -c -C 65001 -t~

In the query the last character is ¸ - i mean QUOTENAME('¸','""')

But when the file is generated, the last character is coming as ?

what causes the character change while doing a BCP ?

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


How did you execute the BCP tool? In Command Prompt window?


0 Votes 0 ·

Hi .

The BCP is executed from a C# console application using the below code. This error is happening for a specific database alone. I have tried the code for a different database, and the output is coming as shown in query. Does the data being in - Unicode, double byte characters , have an impact to this conversion ?

 public static void ExecuteProcess(string fileName, string Query, string servername, string dbinstance)
          {
  using (var process = new Process())
                {
        
                    process.StartInfo.UseShellExecute = false;
                    process.StartInfo.CreateNoWindow = true;
                    process.StartInfo.RedirectStandardError = true;
                    process.StartInfo.RedirectStandardOutput = true;
                    process.StartInfo.FileName = "BCP";
        
                    process.StartInfo.Arguments = "\"" + Query + " \" queryout " + "\"" + fileName.Trim() + "\"" + " -T -S " + servername + " -d " + dbinstance + " -b 500 -c -C 65001 -t~";           
        
                    process.Start();
                        
                    string outputString = process.StandardOutput.ReadToEnd();
                    string errorString = process.StandardError.ReadToEnd();
  }
                }


0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Keep in mind that BCP runs in the command-line window which uses your OEM code page. So that cedilla is likely to be interpreted as something else, and then it goes downhill from there.

Change '¸'to nchar(184) to avoid the issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited

How did you view the file to determine the value was ?. That is like a display problem in your viewer and not an output problem from BCP.

Your viewer is displaying ? because it doesn't know how to display the value 184.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
1 Vote"
Criszhan-msft answered

Hi,



"SELECT 1 as First,quotename(COUNTRY,'""') as COUNTRY, STATUS, QUOTENAME('¸','""') as last FROM TableName "queryout "path of file"

When I try to test this sql statement in a database (Chinese_PRC_CI_AS) different from the default server collation (SQL_Latin1_General_CP1_CI_AS), the last character is coming as ? in returned results. Then executed the BCP command to output the data to the file and got the same result.

I Change '¸' to nchar(184) as Erland suggested, and the problem was solved.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.