Hi,
first of all, thanks a lot for picking up on dbt-sqlserver @cody-scott and everyone else contributing!!
We tested our project with rc2 and everything works fine for now.
One thing I noticed now, when playing around with the unit test feature, which we have not used before, I get an error.
I oversimplify here, but the result is the same for more complex tests.
Model to be tested, called mawi_unit_test
select
ik
,standort_id
from
{{ ref('mawi_psta_beschaffung') }}
Unit test file
unit_tests:
- name: ut1 # this is the unique name of the test
model: mawi_unit_test # name of the model I'm unit testing
given: # the mock data for your inputs
- input: ref('mawi_psta_beschaffung')
rows:
- {ik: 10}
expect: # the expected output given the inputs above
rows:
- {ik: 10}
Result when running the test:
[0m16:50:59.525562 [debug] [Thread-1 (]: fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")
SQL generated for the unit test that causes the error:
-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
select
ik, 'actual' as "actual_or_expected"
from (
with __dbt__cte__mawi_psta_beschaffung as (
-- Fixture for mawi_psta_beschaffung
select
try_cast(10 as int)
as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
) select
ik
,standort_id
from
__dbt__cte__mawi_psta_beschaffung
) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
select
ik, 'expected' as "actual_or_expected"
from (
select
try_cast(10 as int)
as ik
) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
Looking at the SQL, it is easy to fix, the internal WITH clause is not allowed, so one could rewrite it as a derived table for example like this:
-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
select
ik, 'actual' as "actual_or_expected"
from (
select
ik
,standort_id
from
(
-- Fixture for mawi_psta_beschaffung
select
try_cast(10 as int)
as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
) __dbt__cte__mawi_psta_beschaffung
) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
select
ik, 'expected' as "actual_or_expected"
from (
select
try_cast(10 as int)
as ik
) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
Unfortunately, I don't know enough about the inner workings of dbt, so I can't help in changing the code for dbt-sqlserver.
Problem seems to be the nested CTE (which is not supported in sql server). Potential fix is to proxy the query to a temporary table or view, then drop the artifact afterwards.
Hi, first of all, thanks a lot for picking up on dbt-sqlserver @cody-scott and everyone else contributing!!
We tested our project with rc2 and everything works fine for now.
One thing I noticed now, when playing around with the unit test feature, which we have not used before, I get an error.
I oversimplify here, but the result is the same for more complex tests.
Model to be tested, called mawi_unit_test
Unit test file
Result when running the test:
SQL generated for the unit test that causes the error:
Looking at the SQL, it is easy to fix, the internal WITH clause is not allowed, so one could rewrite it as a derived table for example like this:
Unfortunately, I don't know enough about the inner workings of dbt, so I can't help in changing the code for dbt-sqlserver.
Originally posted by @Mira-Roland in https://github.com/dbt-msft/dbt-sqlserver/discussions/516#discussioncomment-10587425