2

I have two databases on the same server, one is a backup of the other from a few weeks ago.

I have a third party application that has started misbehaving. It logs the LastLoginDate of a particular user when they log on using the following command.

Update DBO.theTable Set LastLoginDateTime = '2015/10/27 15:14:34' 
Where UserName = 'theUser'

Now in the live database this works fine, yet when I run the same command on the Backup database I get the following error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This looks like a simple date format error, but I can't find out how to rectify it. From what I understand the language of the database is set at the server level, so this should be the same. Also, the same user at the server level is being used, so again, the Language of that user should not come into play.

As mentioned above, it's a third-party app so I can;t change the underlying SQL to be a different (more universal) date format, so I'll have to handle it at the database level.

Does anyone have any ideas on what I should be checking beyond the above to fix this?

UPDATE

I have worked out a fix to this problem but would still appreciate advice on why the databases are behaving inconsistently. The two databases share a User 'AdminUser'. There are also user accounts set at the server level such as 'AppUser'. 'AppUser' has experienced no problems while I have been investigating this issue. They are set to use British English language

enter image description here

Yet when they log on to the database the database context switches to US English (as captured using SQL Server Profiler)

enter image description here

As a result of this I have switch the AdminUser to have their default Language set to 'English', and now it works as expected

enter image description here

So when I execute the query posted at the top I get the result

(1 row(s) affected)

While the application is now working as expected, it surely can;t be right to have some users set to 'British English', and some set to (US) 'English' to achieve the same behaviour! Any ideas are welcomed.

  • Check the collation of the two databases, make sure they are the same. Are they truly the same database? Did you restore one into another? My first instinct is that one of them is different. – Jonathan Fite Oct 27 '15 at 16:04
  • Logins, sessions and contained databases can use different languages. – T I Oct 27 '15 at 16:04
  • This will help to identify what language you are using when executing your update: SELECT @@language Also this : DBCC USEROPTIONS – Craig Efrein Oct 27 '15 at 16:30
  • See Update above – Jonnus Oct 27 '15 at 16:51
  • @Craig Efrein, AdminUser and AppUser both have their language set to 'British', and the dateformat as 'dmy' when the problem presents itself – Jonnus Oct 27 '15 at 16:52
2

If I understand the problem correctly, when both users have British as default language, one user's request goes through fine because the language setting changes automatically to us_english. But for another user, the language setting doesn't change automatically, hence the error.

The BOL states following regarding default language and how it can be overridden.

The default language for a login can be overridden by using CREATE LOGIN or ALTER LOGIN. The default language for a session is the language for that session's login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs. Note that you can only set the default language option to a language ID defined in sys.syslanguages (0-32). When you are using contained databases, a default language can be set for a database by using CREATE DATABASE or ALTER DATABASE, and for contained database users by using CREATE USER or ALTER USER. Setting default languages in a contained database accepts langid value, the language name, or a language alias as listed in sys.syslanguages.

Based on this the following can affect the language setting.

  • Database default
  • User default
  • DSN setting, connection strings, etc..
  • SET LANGUAGE in session

If underlying statements can't be changed, is there a setting that can affect it in the third party app? Or is there are trigger for a certain user login? Though I believe it's one or a combination of 2 or more in the above list....

  • This led me to the answer! Your mention of the ODBC connection led me to investigate the setup for the DSN setting used. As the two databases each had their own setup this is where the inconsistency lay. There is a setting to "Change the language of SQL Server system messages to (language)" and when this was set to be (US) English explicitly the British English user suddenly was able to insert US format dates. This was confirmed by setting the adminUser back to British English and again, the problems had been resolved. Thanks for your help! – Jonnus Oct 28 '15 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.