Error on table materialization when using non standard database names

[lito1515]

In dbt 1.8, table materialization fails on Azure SQL when database names contains hyphen characters, for instance: "foo-bar-db"

Throwing the following error when dbt run tries to build the table:
('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '-'. (102) (SQLMoreResults)")

Already raised a ticket on dbt-core although this seems to be more related to dbt-sqlserver:
[Regression] Error on table materialization when using non standard database names

Right now, during the materialization process it seems there are sentences where server objects are not properly quoted.
In previous versions they used to be enclosed within square brackets, this way it avoided any issues if objects had a non standard name like in this case containing hyphen characters.

dbt 1.8.6 version generates this sentence which causes error due to the database name not being quoted:

EXEC('
            SELECT * INTO foo-bar-db.tbl.examtable__dbt_tmp FROM "foo-bar-db".tbl.examtable__dbt_tmp__dbt_tmp_vw 
    OPTION (LABEL = ''dbt-sqlserver'');

Whereas dbt 1.7.4 used to have proper naming enclosure and generated the sentence like this:
EXEC('SELECT * INTO [foo-bar-db].[tbl].[examtable__dbt_tmp] FROM [foo-bar-db].[tbl].[examtable__dbt_tmp_temp_view];');

Steps To Reproduce

1. Try creating a new database in Azure SQL and name it using hyphens (i.e. my-precious-db)
2. Create a new model to be materialized as table (a table with just one column will do)
3. Execute dbt run

Environment

• OS: Windows 10.0
• Python: 3.8.8
• dbt (working version): 1.7.4
• dbt (regression version): 1.8.6

Additional Context
dbt-sqlserver adapter was used on both regression and working version. Test was done running it from both Windows and Debian, and both had the same outcome.

Here is the partial log (regression version) showing where the error occurrs:
regression_1_8_6.log

Here is the partial log (working version) showing no error
working_1_7_4.log

Both materializations were done using the same .sql file which contained a very simple statement as follows:
select 1 as id, 'test_description' as concept

it is important to note that in the project yml the quoting for the database is already set:

quoting:
  database: true
  schema: false
  identifier: false

Relevant log output

[0m16:49:45.427118 [debug] [Thread-1 ]: SQL status: OK in 0.000 seconds
�[0m16:49:45.458416 [debug] [Thread-1 ]: dbt-sqlserver
�[0m16:49:45.474066 [debug] [Thread-1 ]: Applying DROP to: "foo-bar-db".tbl.examtable__dbt_tmp__dbt_tmp_vw
�[0m16:49:45.489676 [debug] [Thread-1 ]: dbt-sqlserver
�[0m16:49:45.489676 [debug] [Thread-1 ]: Using sqlserver connection "model.royal_bi.examtable"
�[0m16:49:45.489676 [debug] [Thread-1 ]: On model.royal_bi.examtable: /* {"app": "dbt", "dbt_version": "1.8.6", "profile_name": "default", "target_name": "dev", "node_id": "model.royal_bi.examtable"} */

       USE [foo-bar-db];
       select
           sch.name as schema_name,
           obj.name as view_name
       from sys.sql_expression_dependencies refs
       inner join sys.objects obj
       on refs.referencing_id = obj.object_id
       inner join sys.schemas sch
       on obj.schema_id = sch.schema_id
       where refs.referenced_database_name = 'foo-bar-db'
       and refs.referenced_schema_name = 'tbl'
       and refs.referenced_entity_name = 'examtable__dbt_tmp__dbt_tmp_vw'
       and refs.referencing_class = 1
       and obj.type = 'V'

   OPTION (LABEL = 'dbt-sqlserver');

�[0m16:49:45.520900 [debug] [Thread-1 ]: SQL status: OK in 0.000 seconds
�[0m16:49:45.527422 [debug] [Thread-1 ]: Using sqlserver connection "model.royal_bi.examtable"
�[0m16:49:45.527422 [debug] [Thread-1 ]: On model.royal_bi.examtable: /* {"app": "dbt", "dbt_version": "1.8.6", "profile_name": "default", "target_name": "dev", "node_id": "model.royal_bi.examtable"} */

USE [foo-bar-db];
   EXEC('DROP view IF EXISTS tbl.examtable__dbt_tmp__dbt_tmp_vw;');

�[0m16:49:45.527422 [debug] [Thread-1 ]: SQL status: OK in 0.000 seconds
�[0m16:49:45.543080 [debug] [Thread-1 ]: Writing runtime sql for node "model.royal_bi.examtable"
�[0m16:49:45.558734 [debug] [Thread-1 ]: Using sqlserver connection "model.royal_bi.examtable"
�[0m16:49:45.558734 [debug] [Thread-1 ]: On model.royal_bi.examtable: /* {"app": "dbt", "dbt_version": "1.8.6", "profile_name": "default", "target_name": "dev", "node_id": "model.royal_bi.examtable"} */

   USE [foo-bar-db];
   USE [foo-bar-db];

   USE [foo-bar-db];
   EXEC('
       create view tbl.examtable__dbt_tmp__dbt_tmp_vw as select 1 as id, ''test_description'' as concept;
   ')

EXEC('
           SELECT * INTO foo-bar-db.tbl.examtable__dbt_tmp FROM "foo-bar-db".tbl.examtable__dbt_tmp__dbt_tmp_vw 
   OPTION (LABEL = ''dbt-sqlserver'');

       ')


   EXEC('DROP VIEW IF EXISTS tbl.examtable__dbt_tmp__dbt_tmp_vw')




   use [foo-bar-db];
   if EXISTS (
       SELECT *
       FROM sys.indexes 
       WHERE name = 'tbl_examtable__dbt_tmp_cci'
       AND object_id=object_id('tbl_examtable__dbt_tmp')
   )
   DROP index "tbl"."examtable__dbt_tmp".tbl_examtable__dbt_tmp_cci
   CREATE CLUSTERED COLUMNSTORE INDEX tbl_examtable__dbt_tmp_cci
   ON "tbl"."examtable__dbt_tmp"

�[0m16:49:46.007588 [debug] [Thread-1 ]: SQL status: OK in 0.000 seconds
�[0m16:49:46.007588 [debug] [Thread-1 ]: On model.royal_bi.examtable: ROLLBACK
�[0m16:49:46.007588 [debug] [Thread-1 ]: On model.royal_bi.examtable: Close
�[0m16:49:46.007588 [error] [Thread-1 ]: �[31mUnhandled error while executing target\run\royal_bi\models\datamarts\examtable\examtable.sql�[0m
('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '-'. (102) (SQLMoreResults)")
�[0m16:49:46.028714 [debug] [Thread-1 ]: Traceback (most recent call last):
File "C:\Program Files\Python38\lib\site-packages\dbt\task\base.py", line 368, in safe_run
result = self.compile_and_execute(manifest, ctx)
File "C:\Program Files\Python38\lib\site-packages\dbt\task\base.py", line 314, in compile_and_execute
result = self.run(ctx.node, manifest)
File "C:\Program Files\Python38\lib\site-packages\dbt\task\base.py", line 415, in run
return self.execute(compiled_node, manifest)
File "C:\Program Files\Python38\lib\site-packages\dbt\task\run.py", line 298, in execute
result = MacroGenerator(
File "C:\Program Files\Python38\lib\site-packages\dbt\clients\jinja.py", line 84, in call
return self.call_macro(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\dbt_common\clients\jinja.py", line 299, in call_macro
return macro(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 768, in call
return self._invoke(arguments, autoescape)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 782, in _invoke
rv = self._func(*arguments)
File "", line 149, in macro
File "C:\Program Files\Python38\lib\site-packages\jinja2\sandbox.py", line 394, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 303, in call
return __obj(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\dbt\clients\jinja.py", line 84, in call
return self.call_macro(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\dbt_common\clients\jinja.py", line 299, in call_macro
return macro(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 768, in call
return self._invoke(arguments, autoescape)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 782, in _invoke
rv = self._func(*arguments)
File "", line 52, in macro
File "C:\Program Files\Python38\lib\site-packages\jinja2\sandbox.py", line 394, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\jinja2\runtime.py", line 303, in call
return __obj(*args, **kwargs)
File "C:\Program Files\Python38\lib\site-packages\dbt\adapters\base\impl.py", line 379, in execute
return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch, limit=limit)
File "C:\Program Files\Python38\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 530, in execute
while cursor.nextset():
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '-'. (102) (SQLMoreResults)")

[cody-scott]

Problem seems to be here https://github.com/dbt-msft/dbt-sqlserver/blob/master/dbt/include/sqlserver/macros/relations/table/create.sql#L10 where the relation is being rendered manually, which doesn't apply the dbt quoting policy.

[cody-scott]

This should be fixed in the 1.8.1 release now.