SQL Server: Invalid Column Name
If you are going to ALTER Table column and immediate UPDATE the table including the new column in the same script. Make sure that use GO
command to after line of code of alter table as below.
ALTER TABLE Location
ADD TransitionType SMALLINT NULL
GO
UPDATE Location SET TransitionType = 4
ALTER TABLE Location
ALTER COLUMN TransitionType SMALLINT NOT NULL
Could also happen if putting string in double quotes instead of single.
Whenever this happens to me, I press Ctrl+Shift+R which refreshes intellisense
, close the query window (save if necessary), then start a new session which usually works quite well.
This error may ALSO occur in encapsulated SQL statements e.g.
DECLARE @tableName nvarchar(20) SET @tableName = 'GROC'
DECLARE @updtStmt nvarchar(4000)
SET @updtStmt = 'Update tbProductMaster_' +@tableName +' SET department_str = ' + @tableName exec sp_executesql @updtStmt
Only to discover that there are missing quotations to encapsulate the parameter "@tableName" further like the following:
SET @updtStmt = 'Update tbProductMaster_' +@tableName +' SET department_str = ''' + @tableName + ''' '
Thanks