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

问题描述

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

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

但 Collat​​ion 字在连接字符串中无效.

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 根据应用程序的设置来设置排序规则.我只想在使用像

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

我试图确保对于给定的连接，该连接的所有命令/查询将自动使用 "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

推荐答案

您不能为连接设置排序规则.根本不支持.请参阅 SQL Server Native Client:连接字符串和 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.

关于在非 Unicode 类型上使用排序规则的警告来自 COLLATE (Transact-SQL):

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

char 和 varchar 数据类型支持代码页转换，但 text 数据类型不支持.不报告代码页转换期间的数据丢失.

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.

理想情况下，如果您想从数据中获得一致的多语言支持，您应该使用 Unicode 数据类型(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屋！