generate_surrogate_key fails on SQL Server with one field

[jlkravitz]

The culprit line is here: https://github.com/dbt-labs/dbt-utils/blob/6ba7b660b1d1b4e3b41cb0cf6c3c0e4a70ae54e4/macros/sql/generate_surrogate_key.sql#L27C35-L27C35

select a, b, c, d, {{ dbt_utils.generate_surrogate_key(["a"]) }}
from my_table

results in error

('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The concat function requires 2 to 254 arguments. (189) (SQLMoreResults)')```

I would expect this not to error.

[vinit2107]

Hi @jlkravitz ,
The issue is because of this code.

{% macro sqlserver__concat(fields) -%}
    concat({{ fields|join(', ') }})
{%- endmacro %}

You will need to add length validations here and remove concat if the length = 1 or add an empty string to the list (I prefer this). Happy to contribute if you want me to work on it, or you can take it forward 🙂.

[jlkravitz]

Oh great! I'm happy to take a stab at it.

@vinit2107 Just out of curiosity as I learn this codebase, where can I find the code that dispatches this call to concat to its SQL-backend specific implementation (e.g., SQL Server)?

[vinit2107]

Hi @jlkravitz ,
Sorry for the delay. You can find the code in dbt-core here.

[bleiho-oc]

Hi all, same error for the Micosoft Fabric Adapter.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.