SQL Server 'Invalid column name' in transaction

Question

I have a script where I'm adding a column to the table, and immediately after I populate that column with data from another table. I'm getting 'Invalid column name' error on the column that I am adding.

The error, specifically, is Invalid column name 'tagID'.

The code between BEGIN TRANSACTION and COMMIT is actually an excerpt of a much larger script, but this is the relevant excerpt (and I need all of it to succeed or simply roll back):

BEGIN TRY
BEGIN TRANSACTION
  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL

  MERGE INTO
    Items AS target
  USING
    Tags AS t ON t.tag = target.tag
  WHEN MATCHED THEN
    UPDATE SET target.tagID = t.id;
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
GO

Have you double checked that `Items.tagID` exists? Are you adding `Items.tagID` in the script before the above snippet, in the same batch? Because if so, the parse of the merge statement happens before the column is created and will fail before anything runs. — Shannon Severance, Nov 28 '16 at 20:27
Can you execute the `ALTER TABLE` command on its own without problems? — gmiley, Nov 28 '16 at 20:30
I think this will give you some information to your question. http://stackoverflow.com/questions/7426199/problem-with-alter-then-update-in-try-catch-with-tran-using-transact-sql/7452280#7452280 — DVT, Nov 28 '16 at 20:30
@gmiley Yes, the `ALTER TABLE` command can be run on its own without issue. @Shannon Is there a way around that parse issue or am I stuck with having two separate transactions? — charmeleon, Nov 28 '16 at 20:50
It's nothing to do with transactions. It is a compilation issue. — Martin Smith, Nov 28 '16 at 20:51

score 5 · Accepted Answer · answered Nov 28 '16 at 20:56

SQL Server tries to compile all statements in the batch. If the table doesn't exist compilation of the statement is deferred but there is no deferred compilation for missing columns.

You can use

BEGIN TRY
BEGIN TRANSACTION
  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL
  EXEC('
  MERGE INTO
    Items AS target
  USING
    Tags AS t ON t.tag = target.tag
  WHEN MATCHED THEN
    UPDATE SET target.tagID = t.id;
 ')
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
GO

To push the usage of the column into a child batch compiled after the column is created. It still belongs to the same transaction opened in the parent scope.

Rahul · Answer 2 · 2016-11-28T20:33:38.130

0

Transaction scope are for DML operations and not for DDL operations. So not sure why you are having the ALTER statement in the same transaction block. If not wrong, you should be having that ALTER statement outside the transaction block.

  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL
BEGIN TRANSACTION
  MERGE INTO
  .....

Also, I would remove those [] from the datatype of the column from your ALTER statement

ALTER TABLE [Items] ADD tagID UniqueIdentifier;

edited Nov 28 '16 at 20:33

answered Nov 28 '16 at 20:27

Rahul

76,197
13
71
125

I'm confused, according to http://stackoverflow.com/a/4736346/1670610, transactional DDL is a thing in SQL Server. Are you saying that I can't combine DML and DDL in the same transaction or that DDL cannot be in a transaction in SQL Server? (Pardon my ignorance, I've worked only with MySQL to date) – charmeleon Nov 28 '16 at 20:41
@charmeleon, looks like DDL commands are also part of transaction in `SQL Server` at least but generally DML's are wrapped in trans block. Sp yes you can have it in transaction ... but in your case, check the `ALTER` statement and try having it outside the transaction block. may be have it in a separate trans block – Rahul Nov 28 '16 at 20:47

SQL Server 'Invalid column name' in transaction

2 Answers2