无法解决临时表和 sys.objects 之间的排序规则冲突 [英] Cannot resolve the collation conflict between temp table and sys.objects
本文介绍了无法解决临时表和 sys.objects 之间的排序规则冲突的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下 T-SQL 代码:
The following T-SQL code:
CREATE TABLE #exclude(name VARCHAR(256))
INSERT INTO #exclude VALUES('someprefix_someprocedure')
SELECT 'someschema.' + sys.objects.name
FROM sys.objects
LEFT JOIN #exclude ON sys.objects.name = #exclude.name
WHERE sys.objects.name LIKE 'someprefix_%'
AND type IN ('FN', 'TR', 'P')
AND #exclude.name IS NULL
ORDER BY sys.objects.name ASC
返回此错误:
消息 468,级别 16,状态 9,第 4 行
无法解决SQL_Latin1_General_CP1_CI_AS
在equal to操作中的排序冲突.
Cannot resolve the collation conflict between Danish_Norwegian_CI_AS
and SQL_Latin1_General_CP1_CI_AS
in the equal to operation.
我尝试将其附加到查询中,但它仍然返回相同的错误:
I tried appending this to the query, but it still returns the same error:
COLLATE SQL_Latin1_General_CP1_CI_AS ASC
我该如何解决这个问题?
How can I fix this?
推荐答案
在 WHERE 或 ON (JOIN) 子句中将以下内容添加到产生此错误的任何字段中:COLLATE DATABASE_DEFAULT
.
Add the following to any field that produces this error, in the WHERE or ON (JOIN) Clause:
COLLATE DATABASE_DEFAULT
.
例如(你上面的问题)
CREATE TABLE #exclude(name VARCHAR(255))
INSERT INTO #exclude VALUES('someprefix_someprocedure')
SELECT 'someschema.' + sys.objects.name
FROM sys.objects
LEFT JOIN #exclude ON sys.objects.name COLLATE DATABASE_DEFAULT = #exclude.name COLLATE DATABASE_DEFAULT
WHERE sys.objects.name LIKE 'someprefix_%'
AND type IN ('FN', 'TR', 'P')
AND #exclude.name IS NULL
ORDER BY sys.objects.name ASC
这篇关于无法解决临时表和 sys.objects 之间的排序规则冲突的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文