SQL Server collation and temp tables · Fri Aug 26, 05:01 PM

If you receive an error along the lines of “Cannot resolve collation conflict for equal to operation,” and you’re using a temp table, a table variable in a stored proc, or a table-valued UDF, the probable cause is a mismatch between the collation settings for your current database and your tempdb. There are a few different ways to fix this, but the easiest is probably to append “COLLATE database_default” to the table declarations in the UDF or proc.

Example:
Before being fixed:

CREATE FUNCTION dbo.ReturnMyTable (
@ParamVal nvarchar (2000)
)
RETURNS
@RtnTable table
(
EachItem nvarchar (450)
)
AS
BEGIN
-- Fill in functionality here
END


After fix:

CREATE FUNCTION dbo.ReturnMyTable (
@ParamVal nvarchar (2000)
)
RETURNS
@RtnTable table
(
EachItem nvarchar (450) COLLATE database_default
)
AS
BEGIN
-- Fill in functionality here
END


-- David --

...

CD of the Week, 21 Aug 2005     Some more random stuff