Date_spine macro not able to pass full date range to SQL Server

[axelv6]

I get an error when I want to create a date dimension based on the date spine macro using a range of 1900-01-01 till 2999-01-01 because of an error:
Error: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. (103003) (SQLExecDirectW)'

Note that apparently the date spine macro is able to generate dates using below script is from 1900-01-01 till 1933-06-21. Starting from the 1933-06-22 it fails.

Copying the script from the compiled folder and pasting it direct in SQL Management Studio seems to cause no problem. It appears that the issue is in passing the compiled script to SQL Server.

Steps to reproduce:

with

Base_Date as (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('1900-01-01' as date)",
        end_date="cast('2999-12-31' as date)"
        )
    }}
)
select * from Base_Date

Tested on:
SQL Server 14.0.3445.2
Dbt core: 1.1.2
Dbt synapse: 1.1.0
Dbt sqlserver: 1.1.0

[axelv6]

After further testing it seems that Azure Synapse isn't able to handle the large script. Is it possible to optimize the macro to allow to pass larger date ranges to Synapse?

[matsonj]

Seems like this might be something to pick up in tsql-utils, as we need a shim to handle this use case. The CTE patterns ascribed by the dbt folks do not generally play nice with SQL Server.