SQL Server的Collate语句需注意

核心提示： SELECT *FROM ::fn_helpcollations() SQL Server can support only code pages that are supported by the underlying operating system. When you perform

SELECT *FROM ::fn_helpcollations()

SQL Server can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. These actions can include:

Specifying a default collation for a database when you create or alter the database.

Specifying a collation for a column when creating or altering a table.

When restoring or attaching a database, the default collation of the database and the collation of any char, varchar, and text columns or parameters in the database must be supported by the operating system.

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.

If the collation specified or the collation used by the referenced object, uses a code page not supported by Windows®, SQL Server issues error. For more information, see the Collations section in the SQL Server Architecture chapter of the SQL Server Books Online.

当时，我承认，我确实大致看完了全篇了，心里明白是排序规则的原因，导致了错误信息的出现。使用collate语句强制指定排序规则是可以解决的，于是我在老大的代码上的每个字串类型的字段后面都加上了 collate Chinese_PRC_CI_AS ，然后F5运行，faint...，问题照旧。于是改为：collate SQL_Latin1_General_CP1_CI_AS,嗯，问题解决，正当以为就这样可以解决的时候，我又试了一下没加之前没错的database,faint...,他们出现了同样的错误信息，难道是拆东墙补西墙。不行， 问题没有解决，于是，我也上QQ群发问了，也不知是因为今天是周末还是什么原因，总之没有一个人回答我。最后实在没有办法，只好自己再回来看上面那段其实我并不喜欢的帮助啦（因为是英文嘛！呵呵...）,当我看到

You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

着实把我喜了一把。马上改用collate database_default,嗯，一个通过、两个通过、三个通过....

OK,终于解决，松了一口气。

将这件事post上来，一是对自己作个警示：以后看帮助真的要仔细点。二是希望朋友不要犯类似的低级错误，以免浪费无谓的时间。如果要查看源码sql script,请点击这里下载。是提取database的属性的哦。