SQL Server 2000排序规则问题 [英] SQL Server 2000 Collation Problem

问题描述

大家好，

我正在使用SQL Server 2000数据库，该数据库通过链接服务器与远程计算机上的SQL Server 2005数据库进行交互.
一切正常，除了更新远程计算机上源表中的某些列:我总是得到以下消息:无法解决等于操作的排序规则冲突".

我正在使用临时表(例如#TempTbl)在查询源表时在其中存储结果，我想使用相同的结果来更新由客户端读取"列的源表-但它总是失败. br/>
在比较之前，我也将本地排序规则(#TempTbl)更改为远程数据库排序规则，但这无济于事.

有人可以告诉我我做错了吗，还是这个问题与#TempTbl有关?有没有人可以替代我的问题?

提前谢谢.

d-as_2009

Hi all,

I''m using a SQL Server 2000 DB which interacts with an SQL Server 2005 DB on a remote machine via linked server.
Every thing works fine, except updating some columns in the source-tables on the remote machine:"Cannot resolve collation conflict for equal to operation" is the message I always get.

I''m working with temporary tables (#TempTbl e.g.) where I store results when I query the source tables and I want to use the same results to update the source tables "read by client"-column - but it always fails.

Ialso changed the collation locally (#TempTbl) to the remote DBs collation before comparing it but it does not help.

Can anybody tell me what I''m doing wrong or is this issue related to the #TempTbl? Has anybody an alternative solution to my problem?

Thanks in advance.

d-as_2009

推荐答案

排序规则为unicode数据类型和代码页字符定义了代码页排序顺序.您收到的错误告诉您两列之间的定义不匹配，因此您需要明确指定应使用的排序规则，例如:

A collation defines the code page sort order for unicode data types and code page characters. The error you get tells you that the definition between the two columns doesn''t match so you need to explicitly specify what collation should be used, like this:

SELECT id
  FROM TmpTable1
  INNER JOIN TmpTable2
  WHERE TmpTable1.name COLLATE DATABASE_DEFAULT = TmpTable2.name COLLATE DATABASE_DEFAULT 

祝你好运！

Good luck!

感谢E.F. Nijboer的帖子.您提供的解决方案我已经检查过-没有成功(...我也将本地排序规则(#TempTbl)更改为远程数据库排序规则" ...).

但是我可能找到了解决此问题的另一种方法(感谢http://onlytalkingsense.wordpress.com):

...解决此问题的最简单方法是让SQL Server通过SELECT INTO语句为您创建临时表:

SELECT名称，firstName
从某处
INTO #MyTempTable

临时表仍将在tempdb中创建，但是它的字段将从源中进行整理(在这种情况下，是< somewhere>表中的字段."...

Thanks E.F. Nijboer for your post. The solution you provide I ''ve already checked - with no success (... "I also changed the collation locally (#TempTbl) to the remote DBs collation" ...).

But I probably found another way to fix this problem (thanks to http://onlytalkingsense.wordpress.com):

..."The easiest way to fix this is to let SQL Server create the temp table for you via a SELECT INTO statement:

SELECT name, firstName
FROM somewhere
INTO #MyTempTable

The temp table will still be created in tempdb, but it’s fields will take the collation from their source (in this case, the fields in the <somewhere> table." ...

这篇关于SQL Server 2000排序规则问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！