Azure SQL database table variable collation

Question

Azure SQL Servers have a fixed collation of SQL_Latin1_General_CP1_CI_AS. I have an Azure SQL database with a collation of Latin1_General_CI_AS. I seem to be getting collation conflict errors when creating a table variable and joining to a database table. Here is the code:

DECLARE @resultingRoles TABLE ([MemberRoleName] NVARCHAR(256) NOT NULL PRIMARY KEY)

INSERT INTO @resultingRoles
SELECT DISTINCT([MemberRoleName]) FROM [RolesInRoles]
    WHERE
[ApplicationName] = @applicationName AND
[MemberRoleName]  IN (@role0,@role1,@role2)
WHILE (@@ROWCOUNT>0)
    BEGIN   
    INSERT INTO @resultingRoles
    SELECT DISTINCT([roles].[TargetRoleName])
    FROM [RolesInRoles] AS [roles] 
    INNER JOIN @resultingRoles sj ON sj.[MemberRoleName] = [roles].[MemberRoleName] 
    LEFT JOIN @resultingRoles lf on [roles].[TargetRoleName] = lf.[MemberRoleName]
    WHERE lf.[MemberRoleName] IS NULL
    AND [roles].[ApplicationName] = @applicationName
END

This results in the following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I presume this is because the tabel variable column MemberRoleName is being created with the SQL Server collation NOT the database collation. My expectation was that the database collation would be used - am I wrong?

Is there any way to check the collation on a table variable?

Edit: I cannot change this SQL code to explicitly set the collation.

SQLApostle SQLApostle · Accepted Answer · 2018-06-18T13:02:21

Try using

 DECLARE @resultingRoles TABLE ([MemberRoleName] NVARCHAR(256) COLLATE Latin1_General_CI_AS NOT NULL PRIMARY KEY)

Azure SQL database table variable collation

1 Answers