HI for starter i’m on MacOs, using a sql_server on docker.
Ive a project with multiples models. I’ve an issue.
Everything was working fine with the first model ive created. And recently it began to get stuck when i try to run it.
Here is my log :
(base) florentsaussay@air-de-florent models % dbt --debug run --select TD_ACA_FACTURES.sql --full-refresh
10:47:03 Sending event: {‘category’: ‘dbt’, ‘action’: ‘invocation’, ‘label’: ‘start’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1041bcf90>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x103df8810>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104226fd0>]}
10:47:03 Running with dbt=1.8.7
10:47:03 running dbt with arguments {‘printer_width’: ‘80’, ‘indirect_selection’: ‘eager’, ‘log_cache_events’: ‘False’, ‘write_json’: ‘True’, ‘partial_parse’: ‘True’, ‘cache_selected_only’: ‘False’, ‘profiles_dir’: ‘/Users/florentsaussay/.dbt’, ‘version_check’: ‘True’, ‘fail_fast’: ‘False’, ‘log_path’: ‘/Users/florentsaussay/ACA/logs’, ‘warn_error’: ‘None’, ‘debug’: ‘True’, ‘use_colors’: ‘True’, ‘use_experimental_parser’: ‘False’, ‘no_print’: ‘None’, ‘quiet’: ‘False’, ‘empty’: ‘False’, ‘warn_error_options’: ‘WarnErrorOptions(include=, exclude=)’, ‘static_parser’: ‘True’, ‘introspect’: ‘True’, ‘invocation_command’: ‘dbt --debug run --select TD_ACA_FACTURES.sql --full-refresh’, ‘target_path’: ‘None’, ‘log_format’: ‘default’, ‘send_anonymous_usage_stats’: ‘True’}
10:47:04 Sending event: {‘category’: ‘dbt’, ‘action’: ‘project_id’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104cd5390>]}
10:47:04 Sending event: {‘category’: ‘dbt’, ‘action’: ‘adapter_info’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x103057490>]}
10:47:04 Registered adapter: sqlserver=1.8.4
10:47:04 checksum: 4af21dafb485259c48497ac86b711ddb1982f3d0f1c0ca4e09356de488b753c0, vars: {}, profile: , target: , version: 1.8.7
10:47:05 Partial parsing enabled: 0 files deleted, 0 files added, 1 files changed.
10:47:05 Partial parsing: updated file: ACA://models/TD_ACA_FACTURES.sql
10:47:05 [WARNING]: Deprecated functionality
The tests config has been renamed to data_tests. Please see
Add data tests to your DAG | dbt Developer Hub for more
information.
10:47:05 Sending event: {‘category’: ‘dbt’, ‘action’: ‘deprecation’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘property_’: ‘warn’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104f3b910>]}
10:47:05 [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.ACA.example
10:47:05 Sending event: {‘category’: ‘dbt’, ‘action’: ‘load_project’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x105293dd0>]}
10:47:05 Sending event: {‘category’: ‘dbt’, ‘action’: ‘resource_counts’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10522c410>]}
10:47:05 Found 7 models, 15 data tests, 1 source, 494 macros
10:47:05 Sending event: {‘category’: ‘dbt’, ‘action’: ‘runnable_timing’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x105225710>]}
10:47:05
10:47:05 Acquiring new sqlserver connection ‘master’
10:47:05 Acquiring new sqlserver connection ‘list_ACA’
10:47:05 dbt-sqlserver
10:47:05 Using sqlserver connection “list_ACA”
10:47:05 On list_ACA: /* {“app”: “dbt”, “dbt_version”: “1.8.7”, “profile_name”: “ACA”, “target_name”: “dev”, “connection_name”: “list_ACA”} */
select name as [schema]
from sys.schemas with (nolock)
OPTION (LABEL = ‘dbt-sqlserver’);
10:47:05 Opening a new connection, currently in state init
10:47:05 sqlserver adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost,1433;Database=ACA;UID={sa};PWD=**;encrypt=Yes;TrustServerCertificate=Yes;APP=dbt-sqlserver/1.8.4
10:47:06 sqlserver adapter: Connected to db: ACA
10:47:06 SQL status: OK in 0.000 seconds
10:47:06 On list_ACA: Close
10:47:06 Re-using an available connection from the pool (formerly list_ACA, now list_ACA_NRJBI_STG)
10:47:06 dbt-sqlserver
10:47:06 Using sqlserver connection “list_ACA_NRJBI_STG”
10:47:06 On list_ACA_NRJBI_STG: / {“app”: “dbt”, “dbt_version”: “1.8.7”, “profile_name”: “ACA”, “target_name”: “dev”, “connection_name”: “list_ACA_NRJBI_STG”} */
USE [ACA];
with base as (
select
DB_NAME() as [database],
t.name as [name],
SCHEMA_NAME(t.schema_id) as [schema],
‘table’ as table_type
from sys.tables as t with (nolock)
union all
select
DB_NAME() as [database],
v.name as [name],
SCHEMA_NAME(v.schema_id) as [schema],
‘view’ as table_type
from sys.views as v with (nolock)
)
select * from base
where [schema] like ‘NRJBI_STG’
OPTION (LABEL = 'dbt-sqlserver');
10:47:06 Opening a new connection, currently in state closed
10:47:06 sqlserver adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost,1433;Database=ACA;UID={sa};PWD=**;encrypt=Yes;TrustServerCertificate=Yes;APP=dbt-sqlserver/1.8.4
10:47:06 sqlserver adapter: Connected to db: ACA
10:47:06 SQL status: OK in 0.000 seconds
10:47:06 On list_ACA_NRJBI_STG: ROLLBACK
10:47:06 On list_ACA_NRJBI_STG: Close
10:47:06 Sending event: {‘category’: ‘dbt’, ‘action’: ‘runnable_timing’, ‘label’: ‘6734e6dc-5577-4f37-ac7b-dd362c3645d7’, ‘context’: [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x102b53a90>]}
10:47:06 On master: COMMIT
10:47:06 Concurrency: 1 threads (target=‘dev’)
10:47:06
10:47:06 Began running node model.ACA.TD_ACA_FACTURES
10:47:06 1 of 1 START sql table model NRJBI_STG.TD_ACA_FACTURES … [RUN]
10:47:06 Re-using an available connection from the pool (formerly list_ACA_NRJBI_STG, now model.ACA.TD_ACA_FACTURES)
10:47:06 Began compiling node model.ACA.TD_ACA_FACTURES
10:47:06 Writing injected SQL for node “model.ACA.TD_ACA_FACTURES”
10:47:06 Began executing node model.ACA.TD_ACA_FACTURES
10:47:06 dbt-sqlserver
10:47:06 Applying DROP to: “ACA”.“NRJBI_STG”.“TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw”
10:47:06 dbt-sqlserver
10:47:06 Using sqlserver connection “model.ACA.TD_ACA_FACTURES”
10:47:06 On model.ACA.TD_ACA_FACTURES: / {“app”: “dbt”, “dbt_version”: “1.8.7”, “profile_name”: “ACA”, “target_name”: “dev”, “node_id”: “model.ACA.TD_ACA_FACTURES”} */
USE [ACA];
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 = 'ACA'
and refs.referenced_schema_name = 'NRJBI_STG'
and refs.referenced_entity_name = 'TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw'
and refs.referencing_class = 1
and obj.type = 'V'
OPTION (LABEL = 'dbt-sqlserver');
10:47:06 Opening a new connection, currently in state closed
10:47:06 sqlserver adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost,1433;Database=ACA;UID={sa};PWD=**;encrypt=Yes;TrustServerCertificate=Yes;APP=dbt-sqlserver/1.8.4
10:47:06 sqlserver adapter: Connected to db: ACA
10:47:06 SQL status: OK in 0.000 seconds
10:47:06 Using sqlserver connection “model.ACA.TD_ACA_FACTURES”
10:47:06 On model.ACA.TD_ACA_FACTURES: / {“app”: “dbt”, “dbt_version”: “1.8.7”, “profile_name”: “ACA”, “target_name”: “dev”, “node_id”: “model.ACA.TD_ACA_FACTURES”} */
USE [ACA];
EXEC('DROP view IF EXISTS "NRJBI_STG"."TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw";');
10:47:06 SQL status: OK in 0.000 seconds
10:47:06 Writing runtime sql for node “model.ACA.TD_ACA_FACTURES”
10:47:06 Using sqlserver connection “model.ACA.TD_ACA_FACTURES”
10:47:06 On model.ACA.TD_ACA_FACTURES: /* {“app”: “dbt”, “dbt_version”: “1.8.7”, “profile_name”: “ACA”, “target_name”: “dev”, “node_id”: “model.ACA.TD_ACA_FACTURES”} */
USE [ACA];
USE [ACA];
USE [ACA];
EXEC('
create view "NRJBI_STG"."TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw" as
WITH prepared_data AS (
SELECT
ID,
Ligne_Facture,
REPLACE(REPLACE(REPLACE(REPLACE(Ligne_Facture, ‘’{“‘’, ‘’&‘’), ‘’”}‘’, ‘’$‘’),‘’“{‘’, ‘’&‘’),‘’}”‘’,‘’$‘’) AS modified_ligne_facture
FROM “ACA”.“NRJBI_STG”.“TD_ACA_STAGING”
),
extracted_tags AS (
SELECT
ID,
Ligne_Facture,
modified_ligne_facture,
CASE
WHEN
(
SELECT TOP 1
t.c.value(‘’(/r[16]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
= ‘’’’ THEN NULL
WHEN LEFT(
(
SELECT TOP 1
t.c.value(‘’(/r[16]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, 2) = ‘’"&’’ THEN
(
SELECT TOP 1
t.c.value(‘’(/r[1]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’&‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, ‘’&’‘, ‘’&’‘), ‘’$’‘, ‘’’‘) + ‘’’’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[16]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS split_16_temp,
CASE
WHEN LEFT(
(
SELECT TOP 1
t.c.value(‘’(/r[16]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, 2) = ‘’"&’’ THEN 1
ELSE 0
END AS bool_16
FROM prepared_data
),
x1_calculation AS (
SELECT
*,
CASE
WHEN bool_16 = 1 THEN
CASE
WHEN
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
IS NOT NULL AND
(
SELECT TOP 1
t.c.value(’‘(/r[3]/text())[1]’‘, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(’‘’’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
!= ‘’’’ THEN
CONCAT(
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, ‘’$’‘,
(
SELECT TOP 1
t.c.value(’‘(/r[3]/text())[1]’‘, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(’‘’’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
)
ELSE
(
SELECT TOP 1
t.c.value(’‘(/r[2]/text())[1]’‘, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(’‘’’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END
ELSE modified_ligne_facture
END AS x1
FROM extracted_tags
),
bool_18_calculation AS (
SELECT
*,
CASE
WHEN bool_16 = 1 THEN
CASE
WHEN LEFT(
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, 1) = ‘’&’’ THEN 1
ELSE 0
END
ELSE
CASE
WHEN LEFT(
(
SELECT TOP 1
t.c.value(‘’(/r[18]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, 1) = ‘’&’’ THEN 1
ELSE 0
END
END AS bool_18
FROM x1_calculation
),
final_result AS (
SELECT
ID,
modified_ligne_facture,
REPLACE(REPLACE(split_16_temp, ‘’“&‘’, ‘’{‘’), ‘’$”‘’, ‘’}‘’) AS split_16,
CASE
WHEN bool_16 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[17]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS split_17,
bool_16,
bool_18,
CASE
WHEN bool_16 = 1 AND bool_18 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[1]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’&‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, ‘’&’‘, ‘’&’‘), ‘’$’‘, ‘’’‘) + ‘’’’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_16 = 1 AND bool_18 = 0 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_16 = 0 AND bool_18 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[1]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’&‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
, ‘’&’‘, ‘’&’‘), ‘’$’‘, ‘’’‘) + ‘’’’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[18]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS split_18_temp,
CASE
WHEN bool_16 = 1 AND bool_18 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_16 = 0 AND bool_18 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’$‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE x1
END AS x2,
x1
FROM bool_18_calculation
),
bool_x2_calculation AS (
SELECT
*,
CASE
WHEN bool_16 = 1 AND bool_18 = 1 THEN 2
WHEN bool_16 = 1 AND bool_18 = 0 THEN 1
WHEN bool_16 = 0 AND bool_18 = 1 THEN 2
ELSE 0
END AS bool_x2
FROM final_result
),
final_result_with_18 AS (
SELECT
ID,
modified_ligne_facture,
(
SELECT TOP 1
t.c.value(‘’(/r[1]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS SubscriptionGuid,
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS ResourceGroup,
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS ResourceLocation,
(
SELECT TOP 1
t.c.value(‘’(/r[4]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS UsageDateTime,
(
SELECT TOP 1
t.c.value(‘’(/r[5]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS MeterCategory,
(
SELECT TOP 1
t.c.value(‘’(/r[6]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS MeterSubcategory,
(
SELECT TOP 1
t.c.value(‘’(/r[7]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS MeterId,
(
SELECT TOP 1
t.c.value(‘’(/r[8]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS MeterName,
(
SELECT TOP 1
t.c.value(‘’(/r[9]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS MeterRegion,
(
SELECT TOP 1
t.c.value(‘’(/r[10]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS UsageQuantity,
(
SELECT TOP 1
t.c.value(‘’(/r[11]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS ResourceRate,
(
SELECT TOP 1
t.c.value(‘’(/r[12]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS PreTaxCost,
(
SELECT TOP 1
t.c.value(‘’(/r[13]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS ConsumedService,
(
SELECT TOP 1
t.c.value(‘’(/r[14]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
AS ResourceType,
(
SELECT TOP 1
t.c.value(‘’(/r[15]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(modified_ligne_facture, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’‘) t(c)
)
AS InstanceId,
split_16 AS Tags,
split_17 AS OfferId,
bool_16,
REPLACE(REPLACE(split_18_temp, ‘’"&’‘, ‘’{’‘), ‘’$"’‘, ‘’}’‘) AS AdditionalInfo,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(’‘(/r[4]/text())[1]’‘, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(’‘’’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[2]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[19]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS ServiceInfo1,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[5]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[3]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[20]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS ServiceInfo2,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[6]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[4]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[21]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS ServiceName,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[7]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[5]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[22]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS ServiceTier,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[8]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[6]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[23]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS Currency,
CASE
WHEN bool_x2 = 1 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[9]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
WHEN bool_x2 = 2 THEN
(
SELECT TOP 1
t.c.value(‘’(/r[7]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x2, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
ELSE
(
SELECT TOP 1
t.c.value(‘’(/r[24]/text())[1]‘’, ‘‘nvarchar(max)’’)
FROM (
SELECT CAST(‘’‘’ + REPLACE(REPLACE(x1, ‘’&‘’, ‘’&‘’), ‘’,‘’, ‘’‘’) + ‘’‘’ AS XML) AS x
) a
CROSS APPLY x.nodes(‘’/r’') t(c)
)
END AS UnitOfMeasure,
bool_x2,
bool_18,
x2
FROM bool_x2_calculation
)
SELECT ID, SubscriptionGuid, ResourceGroup, ResourceLocation, UsageDateTime, MeterCategory,
MeterSubcategory, MeterId, MeterName, MeterRegion, UsageQuantity, ResourceRate,
PreTaxCost, ConsumedService, ResourceType, InstanceId, Tags, OfferId, AdditionalInfo,
ServiceInfo1, ServiceInfo2, ServiceName, ServiceTier, Currency, UnitOfMeasure,
bool_x2, bool_18, bool_16, modified_ligne_facture, x2
FROM final_result_with_18;
')
EXEC(’
SELECT * INTO “ACA”.“NRJBI_STG”.“TD_ACA_FACTURES__dbt_tmp” FROM “ACA”.“NRJBI_STG”.“TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw”
OPTION (LABEL = ‘‘dbt-sqlserver’’);
')
EXEC('DROP VIEW IF EXISTS NRJBI_STG.TD_ACA_FACTURES__dbt_tmp__dbt_tmp_vw')
use [ACA];
if EXISTS (
SELECT *
FROM sys.indexes with (nolock)
WHERE name = 'NRJBI_STG_TD_ACA_FACTURES__dbt_tmp_cci'
AND object_id=object_id('NRJBI_STG_TD_ACA_FACTURES__dbt_tmp')
)
DROP index "NRJBI_STG"."TD_ACA_FACTURES__dbt_tmp".NRJBI_STG_TD_ACA_FACTURES__dbt_tmp_cci
CREATE CLUSTERED COLUMNSTORE INDEX NRJBI_STG_TD_ACA_FACTURES__dbt_tmp_cci
ON "NRJBI_STG"."TD_ACA_FACTURES__dbt_tmp"
It stays stuck there. I dont have this problem with my other models.
I’ve tried load of things to fix it, didnt work. And i’ve to manually delete the tmps table and view dbt creates using Azure Data Studio.
Can someone help me ?