sqlserver__alter_column_type - Not working for column change

[Stefan-Rann]

Hi,
this macro generates an error on a simple length adjustment of a column.
I am using SQL Server 2022 (on prem) with dbt-core 1.3.2 and dbt-sqlserver 1.3.1.

To replicate setup an incremental model.

1. Setup the your table to include a column like varchar(50), alter the column to include varchar (5000) sql below
2. Run your model, which will trigger the alter_column_type macro
3. It will fail

The change generates this code:

alter table "mydb"."myschema"."myschema_accounting_sequences_processed" add dwh_business_key__dbt_alter character varying(50);
update "mydb"."myschema"."myschema_accounting_sequences_processed" set dwh_business_key__dbt_alter = dwh_business_key;
alter table "mydb"."myschema"."myschema_accounting_sequences_processed" drop column dwh_business_key;
exec sp_rename '"mydb".myschema.myschema_accounting_sequences_processed.dwh_business_key__dbt_alter', 'dwh_business_key', 'column'

The code results in this error:

('42S22', '[42S22] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid column name "dwh_business_key__dbt_alter". (207) (SQLExecDirectW)')

It is the same error as the last comment of #169

In SQL Server it is not allowed to have an alter table statement dirctly followed by an update statement in the same batch.
You have to use GO in between.
So if you execute the statements line by line, everything works fine. But in a single batch they won't.
I am not sure what this change would mean for consitency and rollback scenarioes in case of errors.
But probably the suggestion from @panasenco in #169 would be a better implementation?

[sdebruyn]

fixed in #376