Can I Create a Temp Table With Collation Different to the Server Collation?

As with virtually all headlines containing a question, the short answer is no. From the research I have done, the temp table will always be created with the collation of the SQL Server. If you want to skip to the solution, click here.

Why would you want a different collation? Well, in this case we need a different collation as there is a third party database which a customisation in Microsoft Dynamics GP is using for pricing lookups. The database was originally on a server with the same collation (SQL_Latin1_General_CP1_CI_AS) as the Dynamics GP server (and which the database shares). However, the database has now been moved to a new server with the default UK server collation (Latin1_General_CI_AS).

The problem now is that when data is being inserted or updated into this database, a collation error is produced:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal operation.
Code: 8004OE14
Source: Microsoft OLE DB Provider for ODBC Drivers

You can check the Server Collation using the following script:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT SERVERPROPERTY('COLLATION')

You can check your database collation using this (replace the highlighted section with the name of the database to check:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
USE DatabaseName
SELECT DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')

In the case I had, the collations were different and this is causing the error on joining tables.

While you can’t change the collation of the entire table, you can change the collation of columns within the temp table.

The below example shows the collation being set on a variety of character type columns (columns such as int or date cannot have the collation changed) while creating my temp table:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE #TempTable (
	VarCharField VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS
	,IntField INT
	,DateField DATE
	,CharField CHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS
	,NvarCharField NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS
)
GO

In the case I had, I needed to collate the columns to SQL_Latin1_General_CP1_CI_AS, but this section can be changed to any collation required.