Error when collation of server instance is different than database instance #85
Description
Hi!
Ran in to an issue running a dbt project on a sql server 2014 instance (I believe the same error would occur on later version but have not tested that)
When the collation of a database is different than the collation set on server level dbt will fail with the error message:
Cannot resolve collation conflict between "SQL
_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_100_CI_AS" in UNION ALL operator occurring in SELECT statement column 2
The reason is the union made in macro sqlserver__get_columns_in_relation where two datasets containing column info from tempdb is union-ed with a dataset containing column data on the specific database. Those two datasets have different collations on the varchar fields and cannot be unioned without specifying collation.
I managed a simple workaround where I collate the columns on the tempdb dataset to the "DATABASE_DEFAULT" collation (the target database). And it seems to work. Though there might be issues in cases I´ve not come across.
So, I added "collate database_default" on columns "column_name" and "data_type" in the tempdb dataset in the sqlserver__get_columns_in_relation macro, resulting in the code below:
{% macro sqlserver__get_columns_in_relation(relation) -%}
{% call statement('get_columns_in_relation', fetch_result=True) %}
SELECT
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
FROM
(select
ordinal_position,
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
from INFORMATION_SCHEMA.COLUMNS
where table_name = '{{ relation.identifier }}'
and table_schema = '{{ relation.schema }}'
UNION ALL
select
ordinal_position,
**column_name collate database_default,
data_type collate database_default,**
character_maximum_length,
numeric_precision,
numeric_scale
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '{{ relation.identifier }}%') cols
order by ordinal_position
{% endcall %}
{% set table = load_result('get_columns_in_relation').table %}
{{ return(sql_convert_columns_in_relation(table)) }}
{% endmacro %}