Language and Collation in SQLServer

DotNetNuke and SQLServer don't always work together in the best of ways, especially if the system culture of the webserver is different from the language setting in sql server. If this is the case, then some modules will have problems saving datetime values to the database. For instance, if the database is expecting German type dates (dd.MM.yyyy), and DNN is sending US type dates (MM/dd/yyyy), in the best case dates will be saved with wrong information (reversed day and month), and in the worst case an error will occur.

 

In SQL server, languages are set on a per user basis, and SQLServer uses a default setting for new users, this default setting is set during installation of SQLserver. If you want to find out what language is used for the sqlserver login that is used by DNN, run this command in host>sql:

 

SELECT @@LANGUAGE

 

(do not check "run as script")

 

If this returns anything else than "us_english", and you are experiencing problems with entering date-time values for some modules, you might be helped by doing the following:

 

EXEC sp_defaultlanguage 'DNNUser', 'us_english'

after you do this, you will need to close your browser and open a new browser window to your site, as changing the default sql server language will cause a temporary error state in dnn. You can verify the new language setting by running SELECT @@LANGUAGE again. This should say "us_english" now

 

Sometimes your sql server might be set up in case sensitive mode, as this is most logical for some languages. However, as us_english is not case sensitive, there is no gurarantee DNN works with case sensitive collations, and in fact, judging from problems some users have reported, the current version of DNN (3.1.1) will not work correctly with case sensitive db collations.

Changing collation of a database is tricky, because the collation setting is rather a default setting used when creating new tables. So if you change the collation of a DB, you should also change the collation of all tables in the DB.

 

Changing db collation has quite a lot of impact, and should never be done from the host>sql screen.... you'll be sure you will kill your DNN setup this way. Furthermore, you should only do this if you are confident changing system settings of your db.

So we use query analyzer for this ;)

 

The first step is changing the db default collation:

 

ALTER DATABASE DotNetNuke SQL_Latin1_General_Cp1254_CI_AS

 

this is the case insensitive collation for Turkish languages (for more info see here: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_5ell.asp)

 

The best moment to change your db collation is when its completely empty (so all new tables will automatically be created in the new collation). However, if your db is not empty, there is no need to panic, you can synchronize all table collations with the new db default. You can find a good script that will automate this here:http://www.databasejournal.com/scripts/article.php/3494951