Cannot resolve collision 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 

It returns this error:

Msg 468, Level 16, State 9, Line 4

Cannot resolve collision conflict between Danish_Norwegian_CI_AS and SQL_Latin1_General_CP1_CI_AS in equal action.

I tried adding this to the request, 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 generates this error in the WHERE or ON (JOIN) section: COLLATE DATABASE_DEFAULT .

eg. (Your question is 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 #exclude table, you need to add COLLATE DATABASE_DEFAULT to any text fields - as shown 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