Cannot resolve the collation conflict between temp table and sys.objects

Question

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

Returns this error:

Msg 468, Level 16, State 9, Line 4

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?

Answer 1

Add the following to any field that produces this error, in the WHERE or ON (JOIN) Clause: COLLATE DATABASE_DEFAULT.

e.g. (Your Question Above)

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

Answer 2

When you create the table #exclude, you'll need to add COLLATE DATABASE_DEFAULT to any text fields - as below:

CREATE TABLE #exclude(name VARCHAR(256) COLLATE DATABASE_DEFAULT NULL)
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