Dealing with number formats when server and client are different locales

[Rob Northcott <Rob.Northcott@xxxxxxxxxxxxxx>]

Our application was written assuming that the SQL server local settings for number formats would be the same as the client machine running the application.

Now there is a need for some clients to run using UK format (full stop for decimal separator) and some to be European format (comma for decimal separator).

This is causing problems with queries sent to the server because each client app is sending query strings in its own local format and the server throws an error if it doesn’t match the server settings.

 

Possible solutions I can think of are:

 

1. Change the client application so it checks and server locale settings and formats numbers appropriately.
2. Change the server settings to match the client (if this can be set PER SESSION?)
3. Set the server to allow both number formats (is this possible?)

 

Obviously solution 1 is possible, but there is a lot of code to go through so we’d rather avoid that unless it is the only way.

Solution 2 sounds good but I can’t find a way of setting server regional settings per session (so different client connections use different settings).  Is this possible?

Solution 3 would be the quickest (laziest?) way if there is a way to have the server accept either . or , as a decimal separator.

 

Any suggestions or hints very welcome before I get stuck into changing the formatting code in the application.

 

Med vänlig hälsning / Best Regards

 

Rob Northcott

Software Developer (UK Office, TEAM Systems)

 

Compilator AB

Södergatan 22

SE-211 34 Malmö

Sweden

www.compilator.com

THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL AND IS THUS FOR USE ONLY BY THE INTENDED RECIPIENT. IF YOU RECEIVED THIS IN ERROR, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL AND ITS ATTACHMENTS FROM ALL COMPUTERS.