Setting collation property in the connection string to SQL Server 2005

Question

I have a ASP.Net web application with connection string for SQL Server 2005 in the web.config.

    Data Source=ABCSERVER;Network Library=DBMSSOCN;Initial Catalog=myDataBase;
User ID=myUsername;Password=myPassword;

I want to specify the collation property in the web.config for different languages like French like

    Data Source=ABCSERVER;Network Library=DBMSSOCN;Initial Catalog=myDataBase;
User ID=myUsername;Password=myPassword;Collation=French_CS_AS

But the Collation word is not valid in the connection string.

What is the correct keyword that we need to use to specify the collation in SQL Server 2005 connection string?

Edit

I understand that collation can be set during the database installation and can also be changed. I do not want to change it permanently in the database. But I want the SQLClient to set the collation based on the application's settings. I only want use it when using SQL Query like

SELECT * FROM TESTTABLE ORDER BY TESTCOLUMN COLLATE French_CS_AS

I am trying to ensure that for a given connection, all the commands/queries for that connection would automatically use the "French_CS_AS" - based on the property setting in the connection string, rather than changing the query definitions

Answer 1

You cannot set collation for a connection. It's simply not supported. See SQL Server Native Client: Connection strings and OLE DB for a really interesting blog article on how connection strings parse out.

You can set a language for a connection. Setting the language for a connection changes how dates are handled and causes system error messages to be provided in the specified language. See Session Language for more information on setting language.

A warning about using collations on non-Unicode types from COLLATE (Transact-SQL):

Code page translations are supported for char and varchar data types, but not for text data type. Data loss during code page translations is not reported.

Ideally, if you want consistent multilingual support from your data you should be using Unicode data types (nvarchar, etc.). You should also see the Collation and International Terminology article on MSDN for more information on this. It contains references to some additional articles that are quite useful as well so don't stop there.