Collation Settings for a Database

Question

Post reply

Collation Settings for a Database

Graham Macfarlane

Mr or Mrs. 500

Points: 539
More actions
October 28, 2005 at 2:04 am

#110727

Does anyone know how to view Collation/regional settings for an individual database? I have used sp_helpsort but it isn't very detailed and does not show me date format etc. The problem has stemmed from the fact that I have 2 copies of the same database restored to 2 different SQL 2000 Servers but one treats dates in US format and one in UK. On the face of it they both have the same collation (Latin1_General_CI_AS).

Viewing 7 posts - 1 through 6 (of 6 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Try

select DATABASEPROPERTYEX('MyDatabase','Collation') as DB_Collation, SERVERPROPERTY('Collation') as Server_Collation

Could this be caused by Windows Regional Settings ?

Please post a follow-up when you find the cause.

SQL = Scarcely Qualifies as a Language

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 2

Thanks Very much. This has confirmed that the database and server collations are identical on both servers. The regional settings are also identical on both servers. I can only assume that something has changed on one of the databases or that there is a difference in the code used by the application. I have people looking into this. The only other thing I can think of is that the Regional Settings on one server have changed since SQL Server was installed.

On one of the servers, the default instance is SQL 7 with a different collation setting, so it may also be possible that the SQL 2000 named instance is picking up something from this even though it is not indicating this.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

I managed to get my local copy of SQL server to reproduce your problem.

Try the following in SQL Query Analyzer

DECLARE @datevar datetime

SET @datevar = '01/02/2005'

SELECT @datevar

The output should be 2005-01-02 00:00:00.000 if not:

Check for "British English" as the default languages at the Instance level AND at the login level. The "English" language uses month/day/year date format but "British English" uses day/month/year date format.

If that is not it:

See if the application is running a "SET FORMAT dmy" command after it connects.

Depending on the driver, there are other ways of setting the language, which affects the date format:

ADO supports a provider-specific language keyword in the ConnectionString.

OLE DB can set the provider-specific SSPROP_INIT_CURRENTLANGUAGE property.

ODBC can specify a language in the data source definition or in a LANGUAGE keyword in the connect string.

You may need to run a SQL Profilier trace.

SQL = Scarcely Qualifies as a Language

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

Hi, unless I am missing something fundamental here, the dateformat is set at the connection level. Check your connections - eg webservers/odbc/query analyzer etc.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

"the dateformat is set at the connection level"

Yes, it can be changed at the connection level, but the default dateformat of a connection is based upon the language setting of the login. The first day of the week is also based on the language.

Run sp_helplanguage to list the supported languages and their defaults.

Here is a summary of the dateformats with a count of the languages:

Count DateFormat

23dmy

1mdy

9ymd

The only language that uses a dateformat of month/day/year is us_english.

The nine languages with a dateformat of year/month/day are:

Japanese, Swedish, Hungarian, Croatian, Latvian, Lithuanian,

Traditional Chinese, Korean, and Simplified Chinese.

SQL = Scarcely Qualifies as a Language

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 6

Thanks for all the help. It turns out that the problem was that 2 different middleware servers were being used here - one of which had been set up with different regional settings. This resulted in the .net framework flipping the dates on one of the servers before it wrote to the database.