Collations can be a bit tricky, especially for
VARCHAR data. For both
VARCHAR data, Collations control sorting and comparison rules. But for
VARCHAR data, Collations also control the character set (i.e. which code page is used to determine what character each 8-bit value represents). So, changing Collations on
VARCHAR data can lead to data loss:
SELECT CHAR(230) AS [DB-Collation],
CHAR(230) COLLATE Korean_100_CI_AS AS [Korean],
CHAR(230) COLLATE Albanian_CI_AS AS [Albanian],
CHAR(230) COLLATE Greek_100_CI_AS AS [Greek];
-- æ a ?
However, since you have a collation mismatch, you need to change one of them. I would advise against using
database_default in this particular case since that will be relative to the database that the query is being executed from, which might lead to inconsistent behavior.
Fortunately, both Collations that are mentioned in the error have the same code page: 1250. You can find this info out using the following query:
SELECT col.[name], COLLATIONPROPERTY(col.[name], 'CodePage') AS [CodePage]
FROM sys.fn_helpcollations() col
WHERE [name] IN (N'Serbian_Latin_100_CI_AS', N'Croatian_CI_AS');
At this point, you need to pick one of those two to force the other one to be the same. Because both Collations use the same code page, the choice won't affect the character set being used. The only difference you need to be concerned about is the rules for sorting and comparison between Serbian and Croatian; pick the one that most closely matches the end-users' expectations.
One option is to force the Collation in the
SELECT statement as it appears you are trying to do, and as is shown in @RLF's answer. The downside here is that you can no longer use
SELECT * (if this is code in a stored procedure it is probably best to not use
SELECT * anyway).
Another option is to force the Collation for one of those tables when you create the temporary table, whether that is done using
CREATE TABLE or
FROM sys.all_columns ac
WHERE ac.[object_id] = OBJECT_ID(N'sys.objects')
AND ac.[name] = N'name';
-- SQL_Latin1_General_CP1_CI_AS (on my system, at least)
SELECT [name] COLLATE Hebrew_100_CI_AS AS [name]
FROM [tempdb].sys.columns sc
WHERE sc.[object_id] = OBJECT_ID(N'tempdb..#coltest');
The benefits here are that you can then:
SELECT * in your
- execute several queries against these tables without needing to add a
COLLATE option to each one.
And, assuming that the default Collation, for the database in which the
CREATE TABLE or
SELECT INTO is being executed, is one of the two Collations noted in the error message, then you can go ahead and use