SQL Server Invalid Column Name After Adding New Column

Invalid column name on sql server update after column create

In this case you can avoid the problem by adding the column as NOT NULL and setting the values for existing rows in one statement as per my answer here.

More generally the problem is a parse/compile issue. SQL Server tries to compile all statements in the batch before executing any of the statements.

When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column. The best way round this is to do the DDL in a different batch from the DML.

If a statement both references a non existing column in an existing table and a non existent table the error may or may not be thrown before compilation is deferred.

You can either submit it in separate batches (e.g. by using the batch separator GO in the client tools) or perform it in a child scope that is compiled separately by using EXEC or EXEC sp_executesql.

The first approach would require you to refactor your code as an IF ... cannot span batches.

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)

      EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');

      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END; 

SQL Updating column after adding it giving Invalid column name error

Your SQL query to do the UPDATE refers to a column that has not yet been created. At compile time, SQL Server detects that the column does not exist, so it gives you the error "Invalid column name 'ExpIsLocalTime'".

In order to include the UPDATE in this query, you will need to encapsulate it in a dynamic SQL query. In other words, something like this:

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 @SQL NVARCHAR(1000)
    SELECT @SQL = N'UPDATE dbo.tbl_SessionsAvailable SET ExpIsLocalTime = 1'
    EXEC sp_executesql @SQL
END
GO

We have the same issue in our SQL scripts that maintain tables. After a table is created, if we add a column to it later, we have to use dynamic SQL to avoid these compilation errors.

SQL Server show invalid column name when the column name on the table exists

You have a DROP statement before the insert. That's why you get an warning that the column is missing.

You may want to move the DROP statement before the CREATE one, or even use the new syntax DROP TABLE IF EXISTS ... if it is available in your edition.

SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

I suspect that you have two tables with the same name. One is owned by the schema 'dbo' (dbo.PerfDiag), and the other is owned by the default schema of the account used to connect to SQL Server (something like userid.PerfDiag).

When you have an unqualified reference to a schema object (such as a table) — one not qualified by schema name — the object reference must be resolved. Name resolution occurs by searching in the following sequence for an object of the appropriate type (table) with the specified name. The name resolves to the first match:

• Under the default schema of the user.
• Under the schema 'dbo'.

The unqualified reference is bound to the first match in the above sequence.

As a general recommended practice, one should always qualify references to schema objects, for performance reasons:

• An unqualified reference may invalidate a cached execution plan for the stored procedure or query, since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query. This results in recompilation of the query/stored procedure, a performance hit. Recompilations cause compile locks to be taken out, blocking others from accessing the needed resource(s).

• Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object (that owned by 'dbo'). This is the usual case. The only time a single probe will resolve the name is if the current user owns an object of the specified name and type.

[Edited to further note]

The other possibilities are (in no particular order):

• You aren't connected to the database you think you are.
• You aren't connected to the SQL Server instance you think you are.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

Insert/ Update Procedure gives Invalid Column Name Error SQL Server

You are getting an error because while inserting data in table 'ProjectBillingSource', you have given column in a single quote, columns name should be specified without single quote.

INSERT INTO ProjectBillingSource(**'projectBillID', 'TransAmount', 'TransDesc', 'TransDate', 'projectID', 'accountMgr'**)
VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectID, @accountMgr)

Update the insert statement without quote like this -

INSERT INTO ProjectBillingSource (projectBillID, TransAmount, TransDesc, TransDate, projectID, accountMgr)
VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectID, @accountMgr)

Also, you should create a table only when it does not exist, like this -

if OBJECT_ID('ProjectBillingSource') is null
begin
    SELECT * INTO ProjectBillingSource FROM ProjectBilling
end

Invalid Column Name issue with SSMS 18

This is a known issue with SSMS 18.9, and a hotfix is currently being developed.

https://twitter.com/SysAdminDrew/status/1382869366702624774?s=20

---

Related Topics