在连接字符串中设置的排序规则属性到SQL Server 2005 [英] Setting collation property in the connection string to SQL Server 2005

问题描述

我在web.config一个ASP.Net Web应用程序的连接字符串为SQL Server 2005。

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;

我要指定在web.config为不同的语言，如法语整理属性像

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.

什么是我们需要使用指定SQL Server中的排序正确的密码2005的连接字符串？

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

修改 *据我所知，归类可以在数据库安装过程中进行设置，也可以改变。我不想永远地改变它在数据库中。但我想SQLCLIENT基于应用程序的设置，设置排序。 我只想要使用像SQL查询时使用SELECT * FROM TestTable的ORDER BY TESTCOLUMN COLLATE French_CS_AS。我想，以确保对于给定的连接，所有的命令/该连接会自动使用基于属性更改查询定义的连接字符串中的设置，而不是French_CS_AS查询*

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*

推荐答案

您不能设置排序规则的连接。它根本不支持。请参阅<一href="http://blogs.msdn.com/b/sqlnativeclient/archive/2009/05/07/sql-server-native-client-connection-strings-and-ole-db.aspx"相对=nofollow> SQL Server本机客户端：连接字符串和OLE DB 有关连接字符串如何解析出一个非常有趣的博客文章

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.

有关使用排序规则对非统一code型从自动分页警告（Transact-SQL中）：

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

code页转换为CHAR和VARCHAR数据类型的支持，而不是文本数据类型。是不是报告说，在code页转换数据丢失。

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.

在理想情况下，如果你想从你的数据是一致的多语言支持，你应该使用统一code数据类型（nvarchar的，等等）。你也应该看到排序规则和国际术语 MSDN上的文章以了解更多信息这一点。它包含对那些还有那么不要停在那里非常有用的一些附加条款。

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.

这篇关于在连接字符串中设置的排序规则属性到SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！