Adding column then updating says invalid column name

Transact-SQL
1

I have the following SQL in SQL Server 2005 but I get an error stating "Invalid column name 'ExpIsLocalTime' (ln 7) when I run it:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
	ALTER TABLE dbo.tbl_SessionsAvailable ADD
		ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0)

	UPDATE	tbl_SessionsAvailable
	SET		ExpIsLocalTime = 1
END
GO

Is there something about BEGIN/END that delays the execution of the DDL statement?

2

In the NOT EXISTS test, you are using square brackets, quotes and a dot separator. In effect you are testing for the presence of a table named "[dbo].[tbl_SessionsAvailable]"; not a table named "tbl_SessionsAvailable" in a schema named "dbo".
That doesn't explain away your issue though. Is it possible that you have a default schema that is NOT named "dbo" that does have a table named "tbl_SessionsAvailable"? If so, try explicitly specifying the dbo schema in the UPDATE statement.

3

Are you sure? I don't have a table named "[dbo].[tbl_SessionsAvailable]" but the test works correctly. SSMS generates SQL using this format. E.G.

/****** Object:  Table [dbo].[a_table1]    Script Date: 05/13/2016 16:47:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_table1]') AND type in (N'U'))
DROP TABLE [dbo].[a_table1]

Good idea, but SELECT SCHEMA_NAME() returns dbo and I get the same result with UPDATE dbo.tbl_SessionsAvailable. dbo should be the only schema in use in this database.

4

Try putting at "go" before the update statement.

5

I stand corrected.
I'm shocked that it works the way it does. And a little chagrined. But corrected I stand nonetheless.

Thank-you.

6

Are you getting this error at run time or compile time? At compile time, the SQL engine would have no knowledge of the (currently) non-existent column and would, naturally, throw an error..

1 Like
7

I think you will have to use dynamic SQL to EXEC the "ALTER TABLE" statement

1 Like
8

I can't because my application parses the script file on "GO"s so it would break it up in to multiple statements.

I'm getting it when I try to execute in SSMS. I'm not sure if executing from my .NET application would behave any differently...I didn't get that far. It seems it fails at compile time so it never gets to run-time.

That was the suggestion I got elsewhere as well, except to execute the UPDATE as dynamic SQL. I've tried it and it seems to work. Seems like a bug to me, though.

9

ALTER TABLE has to be the only statement in the batch.

(Don't suppose terminating the ALTER TABLE statement with ";" is enough to achieve that ? )

10

Since you update all records, you could set default to 1, and change default back to 0 afterwards. Someting like:

alter table dbo.tbl_SessionsAvailable
  add ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime default(1)
;
alter table dbo.tbl_SessionsAvailable
  drop constraint DF_tbl_SessionsAvailable_ExpIsLocalTime
;
alter table dbo.tbl_SessionsAvailable
  add constraint DF_tbl_SessionsAvailable_ExpIsLocalTime default(0) for ExpIsLocalTime
;

And now you don't need the update statement.

11

I'm curious: Is the issue here that the DDL statement must be the only statement in the batch, or that the DML statement references a table that already exists and, as such, the parser requires that all columns must exist?

12

The latter.

1 Like
13

It was not in SSMS.

I didn't want to do it this way to avoid the possibility of the constraint being left set to 1 if the following statements failed to execute for any reason. I'm trying to execute them in the same transaction. The application that applies the SQL parses the whole file on GO separators and executes each command in its own transaction. I do have the ability to span a transaction across multiple commands, but the dynamic SQL approach should execute within the same transaction, right?

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
	ALTER TABLE dbo.tbl_SessionsAvailable ADD
		ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0);

	DECLARE @updateSQL nvarchar(max)
	SELECT	@updateSQL = N'UPDATE tbl_SessionsAvailable SET ExpIsLocalTime = 1'
	EXEC	sp_executesql @updateSQL
END

So that if the dynamic SQL fails, the added column is also rolled back? I still don't really understand why this approach works, if the other one doesn't.

14

Well since you wrap it in a transaction, it will be rolled back in case of an error (same as the dynamic solution). Only difference is, you have to allow for dynamic execution (and you probably already have). So you have two solutions, and its up to you, which one you prefer. :slight_smile:

15

Create the Sproc with column existing, and then Drop the column?