SQL Server error 8003 "too many parameters" on large seed with dbt-sqlserver >= 1.4.1

[NicolasPA]

From dbt-sqlserver >= 1.4.1, when running one of our big seed, we're getting the SQL Server 2016 error 8003:

The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Would there be a query generated by DBT with a very large where condition?

The failing seed is 2.2 MB, 10 columns x 17k rows, including 3 large columns around varchar(250). We have another smaller, but still big, seed that woks just fine with 1.4 MB, 5 columns x 24k rows.

Here's the file slightly anonymized to reproduce the error: big_seed.csv

For now, we're stuck with dbt-sqlserver ==1.3.2 because 1.4.0 has issues with indexes and deadlocks, and versions above have the issue described above with the seed.

Despite this issue, it's been a great experience working with dbt-sqlserver, thanks for your work!

[flunardelli]

Looks like this new version it's not respecting this doc https://docs.getdbt.com/reference/resource-configs/mssql-configs, related to set manually a max_batch_size (sorry if I missed some step here). As a workaround you can create a custom macro an overwrite the default value of 400. {% macro sqlserver__get_batch_size() %} {{ return(100) }} {% endmacro %}

[NicolasPA]

Thank you for pointing out the batch size config! I will try when I can.

[NicolasPA]

Sorry to come back so late, we tested overwriting the macro and it indeed worked, thank you ! We also tried setting max_batch_size but that didn't work.

I think it would still require a systematic solution to avoid this workaround.

[lucaslortiz]

There are two issues here:

1. The max_batch_size var is not working

2. Even though this workaround worked in your case, it is not the final solution. The size of the batch is calculated based on the maximum number of parameters allowed, as you can see below. The issue is that it considered 2100 to be the maximum, while the real value is 2098 (reference here). In some specific cases, it will still fail, even if you set a lower number for the batch - for example, if you have 21 columns and more than 2100 rows, with a batch of 100.

Calculated maximum number of parameters

I'll create a PR shortly.