Hard-coded square brackets for database in sqlserver__get_columns_in_relation macro causes 'Invalid object name' error when using with Linked Server

[ghost]

Summary

Hard-coded square brackets for database in sqlserver__get_columns_in_relation macro causes 'Invalid object name' error when using with Linked Server. Removing the brackets enables the code to run normally.

Error description

When running an operation to generate a base model using the codegen macros, I am faced with the following error:

(.venv) PS C:\Users\...> dbt run-operation generate_base_model --args '{
 >>   "source_name": "dw_refdata",
 >>   "table_name": "exchangerate"
 >> }'
 10:41:47  Running with dbt=1.3.2
 10:41:47  Encountered an error while running operation: Database Error
  ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'SQL01.DW.INFORMATION_SCHEMA.COLUMNS'. (208) (SQLExecDirectW)")

This seems to be caused by the square brackets enclosing {{ relation.database }} and the fact that the referenced database is accessed via a linked server. By removing the square brackets, everything runs normally.

dbt-sqlserver/dbt/include/sqlserver/macros/adapters/columns.sql

Line 13 in c1a7767

from [{{ relation.database }}].INFORMATION_SCHEMA.COLUMNS

Steps for reproduction:

1. Create a source that references a table via a linked server, such as:

version: 2

sources: 
- name: dw_refdata
  database: SQL01.DW
  schema: refdata

2. Run a dbt operation with the codegen package to generate a base model:

dbt run-operation generate_base_model --args '{ "source_name": "dw_refdata", "table_name": "exchangerate" }'

3. The above error should be returned

Steps to fix

1. Remove the square brackets in [{{ relation.database }}] from the columns.sql file in your local installation of dbt-sqlserver in

   dbt-sqlserver/dbt/include/sqlserver/macros/adapters/columns.sql

   Line 13 in c1a7767

   from [{{ relation.database }}].INFORMATION_SCHEMA.COLUMNS

2. Re-run the dbt operation from the reproduction steps
3. Now everything should return as expected:

(.venv) PS C:\Users\...> dbt run-operation generate_base_model --args '{
>>   "source_name": "dw_refdata",
>>   "table_name": "exchangerate"
>> }'
11:07:28  Running with dbt=1.3.2
11:07:28  Unable to do partial parsing because config vars, config profile, or config target have changed
11:07:30  

with source as (

    select * from {{ source('dw_refdata', 'exchangerate') }}

),

renamed as (

    select
        date,
        ratedate,
        currencyid,
        name,

    from source

)

select * from renamed

(.venv) PS C:\Users\...> 

Thoughts on the fix

If the components that use this macro honor the dbt-project quoting configuration and correctly input the quoted values, as would be the correct expected behavior, then removing the hard-coded square brackets should not generate any unintended side-effects.