SQL Management Studio and regional settings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stefan.jansen@hagstofa.is - 29 Jul 2008 10:03 GMT

I'm Running SQL 2005 and Microsoft SQL Server Management Studio
(9.00.3042.00) and experiencing strange behaviour when results from my
queries are displayed.

My regional settings are set to Icelandic (comma as decimal separator
and dot as thousand separators). Running the following queries

SELECT CAST(5 AS NUMERIC(5,2)) / CAST(2 AS NUMERIC(5,2))
SELECT 5.0 / 2.0
SELECT CAST(5 AS float)/ CAST(2 AS float)

Give me following results

2.50000000
2.500000
2,5

Only the last one gives me the result according to my regional
settings.
Anyone else experienced similar problems and/or now a solution to this
problem.

Stefan

Reply to this Message

Erland Sommarskog - 29 Jul 2008 22:29 GMT

> I'm Running SQL 2005 and Microsoft SQL Server Management Studio
> (9.00.3042.00) and experiencing strange behaviour when results from my
[quoted text clipped - 17 lines]
> Anyone else experienced similar problems and/or now a solution to this
> problem.

I reported this during the beta of SQL 2005, and the bug was closed as
"by design". I think the underlying problem is in SqlClient.

There is also the question of what result do we really want? That is,
do we want the comma or the period? I think I prefer the point, because
that works when I paste into a query window. Then again, comma works
better when you paste into Excel.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

bass_player - 30 Jul 2008 04:05 GMT

Regional settings apply to the server side. In my case, running a query from
my client to the server does not reflect the regional setting but it does
when I do the same on the server

> I'm Running SQL 2005 and Microsoft SQL Server Management Studio
> (9.00.3042.00) and experiencing strange behaviour when results from my
[quoted text clipped - 19 lines]
>
> Stefan

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 30 Jul 2008 04:58 GMT

> Regional settings apply to the server side.

Only if you are running the client on the server. If you are using
Management Studio on your workstation and connecting to a remote server, the
local workstation's regional settings (not the server's) influence a lot of
the output in your local tool.

A

Reply to this Message

bass_player - 31 Jul 2008 08:56 GMT

Thanks for the clarification, Aaron. I forgot to mention the effects of
Excel on the client-side querying the server. Apparently, we can't use the
client tools on my workstation to connect to the servers. :-(

>> Regional settings apply to the server side.
>
[quoted text clipped - 6 lines]
>
> A

Reply to this Message