SQL server with german regional settings.

Question

Hi,

We have a database application that has been running for months, then suddenly it started to give errors.

I noticed a datetime float calculation exception that which was being compared with the value 0,05. I also noticed that dates being written to our xml log file were in the format d.mm.yy.

Clearly, the decimal operator being used is ',' and the date separator is '.'.

The application was initially installed using the UK english regional settings. I checked that the regional settings which were still UK english.

I noticed in the database connection string, the host was given as "servername\instance,portNumber", where as I expected "servername\instance:portNumber" for an normal working installation.

Any ideas where the regional settings are obtained from? Also, could it be the user they are using to run apps against the database or the user that runs the database?

JD.

Answer 1

Control panel.. regional settings.

This sounds like a client app issue too, not SQL Server. If they are installed on the same box, SQL Server does not take settings from the OS locale.

It could be that the user of the app has german locale and some data is being passed as string, when it should be float or datetime already (with client doing locale handling)

SQL Server will also never recognise "0,05" auf Deutsch too.

SET LANGUAGE GERMAN

DECLARE @val float
SET @val = 0,05 --fail
GO
DECLARE @dt datetime
SET @dt = '23 Mrz 2009' --fail
GO
DECLARE @dt datetime
SET @dt = '23.03.2009' --ok
GO

And "servername\instance,portNumber" is correct for SQL Server.