question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked ErlandSommarskog commented

Character getting converted to different character

Hi.

I am trying to use BCP from a C# console application.

I am passing a query from my resources file and the query is passed to BCP command.

Example: I have my queries as below :

 SELECT 1 as NameINFO, SEQ_NUM,
 QUOTENAME('¸','""')  AS DELIMITS
 FROM TableName

The last character is getting changed to ?
I am trying to understand what could be the possible reasons for this character getting changed from ¸ to ?

The table collation is Latin general. what are the things which need to be checked in database and server?

sql-server-generalsql-server-transact-sql
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.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

The last character is getting changed to ?

That happens when you use a character which do not exists in the use code page (collation).
Does it work using Unicode with literal N?

  SELECT 1 as NameINFO, SEQ_NUM, QUOTENAME(N'¸', N'""')  AS DELIMITS
  FROM TableName
· 6
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.

yes, it works when i change it to nchar(184) .

However, I have some 200 queries and it would be tough to make the changes. Are there any settings which I need to check in server or database.

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 PadmanabhanVenkatesh-6789 ·

What did you change to nchar(184)?

If you have many queries or files, you can try the “Search and Replace” feature.

0 Votes 0 ·

Hi ,

where should the codepage be checked ? I tried using nchar(184) for the character ¸ for one of the queries.

That worked. But I would like to know, why the character ¸ has an issue and where should it be checked ?

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog PadmanabhanVenkatesh-6789 ·

However, I have some 200 queries and it would be tough to make the changes.

Not particularly. You do have a good text editor, don't you? It should be able to run Find/Replace across many files.

Are there any settings which I need to check in server or database

Not really. This happens in a command-line window, Windows uses the OEM code page, which is a legacy from the days of MS-DOS. In the OEM code page of your machine, the character ¸ is not available, so it is being replaced by a fallback character, which is this case is ? (the generic fallback character, which nothing else fits).

You can change the code page for the command-line window, and maybe you can ask for something else when you submit the command from the C# program. But it is not good to rely on machine settings, because then things break down when you move to a new machine.

By the way, I seem to recall that I have recommended you to use nchar(184) in an earlier thread of yours.

0 Votes 0 ·

Yes, based on your recommendation I had tried using nchar(184).

This character gets changed to ? only for a particular database. For all other databases( which are in different server ) the character works fine.
Hence was thinking , if there is any setting to be checked other than changing the code


0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please change to:

 SELECT QUOTENAME('.','""') AS DELIMITS

Output:
118175-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (863 B)
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.