Real columns are comma separated instead of dot separated ?

Question

Post reply

Real columns are comma separated instead of dot separated ?

gormus

SSC Enthusiast

Points: 178
More actions
April 19, 2020 at 9:53 am
Go to Answer
#3743982

Hi,
installed a new SQL Server Express 2019 and somehow my real columns are now with comma instead of the usual dot when separating number from decimals (viewed in SSMS). I never had this happen before. I installed the same SQL Express 2 weeks ago and it was fine. Does SQL Server now forces my windows localization onto it ? It screws up my code, so I really would like it back if possible. I know I can re-write all code to use FORMAT() but it would be easier just to "flip it back" to dot-separation.
Example of a real column:
Use to show 12345.6789
Now shows: 12345,6789
Thanks.
gormus

SSC Enthusiast

Points: 178
More actions
April 21, 2020 at 1:46 pm
Answer
#3744971

Ok, found out what's causing it, but it doesn't make sense to me.
I ran my code on my other machine, which has SSMS v18.4. My code works fine, and shows columns as expected (with dots in real numbers)
Then I upgraded that same machine to SSMS v18.5. Now it shows commas in real numbers and my code don't work. Just as reported in first post.
I interact with SQL Server via 'ODBC Driver 17 for SQL Server'.
I don't get it. Why does updating the SSMS change how either ODBC works (or something else) ?

Viewing 13 posts - 1 through 12 (of 12 total)

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

go to tools -> options - > Environment -> international settings.

probably set to "same as Microsoft Windows"

but this should not affect your code - unless you are copying/pasting from SSMS output onto another program

gormus SSC Enthusiast Points: 178 More actions · Answer 2

It is already set to 'English' so that's not the problem. But thanks tho for trying.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Not sure but you may also have to for the language to "English", as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gormus SSC Enthusiast Points: 178 More actions · Answer 4

Hi Jeff,

Language on Windows is also en-us. So can't be that either. Thanks for replying tho.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

gormus wrote:

Hi Jeff,
Language on Windows is also en-us. So can't be that either. Thanks for replying tho.

I'm not talking about the language on Windows... I'm talking about the language settings for SQL Server. It could be something else but let's double-check first. Run the following code. The should both be the same and they should both indicate (probably) "us_english".

 SELECT  CurrentSessionLanguage = @@LANGUAGE
        ,CurrentServerLanguage  = lng.name 
   FROM sys.configurations cfg
   JOIN sys.syslanguages   lng on cfg.value_in_use = lng.langid
  WHERE cfg.name = 'default language'
;

Also, if you actually are talking about a "real" column with some form of numeric datatype, using FORMAT isn't going to help here because that type of stuff only works on character based stuff. "real" numeric data isn't stored with a format. Either SSMS is changing it on you during display or some server setting is changing it on you. And, no... I've not had to do this type of thing in SSMS before so we'll both have to look for that little nuance. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gormus SSC Enthusiast Points: 178 More actions · Answer 6

Hi again,

both Session and Server says "us_english".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

K. Thanks. I'll keep looking. This one has me stumped so far.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gormus SSC Enthusiast Points: 178 More actions · Answer 8

gormus

SSC Enthusiast

Points: 178

April 20, 2020 at 8:24 pm

#3744813

Yes, me too. Never had this issue before.

gormus SSC Enthusiast Points: 178 More actions · Answer 9

Ok, case closed.

The comma-thing is only visual. My code wasn't working properly. But v18.5 did however change the visuals of columns, but I can live with that.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

gormus wrote:

Ok, found out what's causing it, but it doesn't make sense to me.
I ran my code on my other machine, which has SSMS v18.4. My code works fine, and shows columns as expected (with dots in real numbers)
Then I upgraded that same machine to SSMS v18.5. Now it shows commas in real numbers and my code don't work. Just as reported in first post.
I interact with SQL Server via 'ODBC Driver 17 for SQL Server'.
I don't get it. Why does updating the SSMS change how either ODBC works (or something else) ?

Lordy. That's gotta be a setting somewhere in the new SSMS just like the setting you've been able to make (forever) to right justify numbers in the grid. Your commas thing is probably buried somewhere under {Tools}{Options}{Results}{Grid} or something like that.

Anyway, like you said, "Case Closed" and I very much appreciate you posting what you finally found.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

I upgraded SSMS to v18.5 and do not see these results - there must be a setting somewhere that is causing this issue, either in SSMS or in that instance of SQL Server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs