CODEWITHSUNDEEP
sql-servercommon-table-expressiontype-conversionsqldatatypessystem-tables
user2163043
user2163043

Reputation: 371

sys.objects.type_desc column won't match with anchor in recursive CTE

I have the following CTE:

;WITH Cte([Type], [Level]) AS 
(
SELECT CONVERT(NVARCHAR(60), 'USER_TABLE'), 1

UNION ALL

SELECT CONVERT(NVARCHAR(60), o.type_desc), Cte.[Level] + 1
FROM sys.objects o
JOIN Cte ON 1 = 1
WHERE Level < 3
)
SELECT * FROM Cte

And it returns me the message 'Types don't match between the anchor and the recursive part in column "Type" of recursive query "Cte".' sys.objects.type_desc is supposed to be an NVARCHAR(60) so the CONVERT shouldn't be needed on it but after getting the error without I added it on to be sure or something.

If I remove the JOIN onto Cte in the recursive part and hardcode [Level] so that the CTE is no longer recursive, the SQL runs fine. If I insert the contents of sys.objects into a temp table and replace sys.objects in the recursive part of the CTE with the temp table, the SQL runs fine. Any attempt to use CAST instead of CONVERT or change the destination type from NVARCHAR(60) to another NVARCHAR or VARCHAR don't change the result.

Any ideas what's going on here?

Thanks

(Also obviously the SQL is useless, it's just an example that reproduces the error I was experiencing with larger code.)

EDIT: Should also add, I'm running SQL Server 2012 here (though I've also tried the same in 2014 and had the same issue).

Upvotes: 0

Views: 217

Answers (1)

Will
Will

Reputation: 473

update: was based on the previous version of the question which was joining onto a #tmp table rather than sys.objects

That looks like a collation difference between tempdb and the current database. Normally happens if you install sql server with one collation and create / restore a DB with a different collation.

The system databases will use the install collation. Most often causes problems with #tmp tables.

(Answer based on the fact that Martin's Smith's suggestion fixed the problem)

Upvotes: 2

Related Questions