Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: Invalid Column Name Error in SQL Stored Procedure

    I have a stored procedure that I am using to convert tables to a new format for a project. The project requires new tables, new fields in existing tables, dropping fields in existing tables and dropping an existing table. The SP takes care of doing all this and copying the data from the tables that are going to be dropped to the correct places. Everything is working fine except for one table and I can't figure out why.

    For this particular table, it already exists in the database and has new fields added to it. Then I try and update those fields with values from another table. This is where I am getting the Invalid column name error (line is highlighted in red). If I comment out the code where the error is occurring and run the update alone everything works fine so I know the Update statement works.

    Here is the specific error message I am getting in SQL Server 2005:
    Msg 207, Level 16, State 1, Line 85
    Invalid column name 'AssignedAgent'.
    Msg 207, Level 16, State 1, Line 85
    Invalid column name 'DateTimeAssigned'.

    Here is the SP: -- Any help is greatly appreciated!

    IF OBJECT_ID('ConvertProofTables','P') IS NOT NULL
    DROP PROCEDURE ConvertProofTables;
    GO

    CREATE PROCEDURE ConvertProofTables
    AS
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    BEGIN TRANSACTION
    -- Add new fields to Proof_Agents Table
    ALTER TABLE Proof_Agents ADD
    IsSignedOn BIT,
    MaxAssignedCVLs INT NULL;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('ALTER TABLE Proof_Agents ERROR', 16,1)
    GOTO _ROLLBACK
    END

    -- Create Proof_Fax Table
    CREATE TABLE Proof_Fax
    ( FaxID INT IDENTITY PRIMARY KEY,
    ProofID INT,
    FaxNumber VARCHAR(10),
    Attn VARCHAR(50),
    CallbackNumber VARCHAR(50),
    UserCode VARCHAR(10) NULL,
    Retries INT NULL,
    FaxStatus VARCHAR(255) NULL,
    FailureReason VARCHAR(255) NULL,
    PagesSent INT NULL,
    TransactionID VARCHAR(19) NULL,
    IsCompleted BIT,
    LastDeliveryDate DATETIME );
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('CREATE TABLE Proof_Fax ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Add new fields to Proof_Policy
    ALTER TABLE Proof_Policy ADD
    AssignedAgent INT NULL,
    DateTimeAssigned DATETIME NULL,
    IsImaged BIT;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('ALTER TABLE Proof_Policy ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Create Proof_Submitter Table
    CREATE TABLE Proof_Submitter
    ( SubmitterID INT IDENTITY PRIMARY KEY,
    ProofID INT,
    SubmitterEmail VARCHAR(50),
    DateTimeSubmitted DATETIME );
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('CREATE TABLE Proof_Submitter ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Pull Fax #, Attn, From Proof_Policy and iscompleted and datetimecompleted from
    -- Proof_Assigned and insert into Proof_Fax
    INSERT INTO Proof_Fax
    (ProofID, FaxNumber, Attn, CallbackNumber, IsCompleted, LastDeliveryDate)
    SELECT PolicyID, Fax1+Fax2+Fax3 AS FaxNumber, Attn, '' AS CallbackNumber, Iscompleted, datetimecompleted
    FROM Proof_Policy
    INNER JOIN Proof_Assigned ON Proof_Policy.PolicyID = Proof_Assigned.proofID;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('INSERT INTO Proof_Fax ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Pull submitteremail from Proof_Policy and datetimesubmitted from Proof_Assigned
    -- insert into Proof_Submitter
    INSERT INTO Proof_Submitter
    (ProofID, SubmitterEmail, DateTimeSubmitted)
    SELECT PolicyID, SubmitterEmail, datetimesubmitted
    FROM Proof_Policy
    INNER JOIN Proof_Assigned ON Proof_Policy.PolicyID = Proof_Assigned.proofID;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('INSERT INTO Proof_Submitter ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Set IsImaged to False for all incomplete CVLs
    UPDATE Proof_Policy
    SET IsImaged = 0
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('UPDATE Proof_Policy IsImaged False ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Set IsImaged to True for all completed CVLs
    UPDATE Proof_Policy
    SET IsImaged = 1
    WHERE Proof_Policy.PolicyID IN (SELECT PolicyID
    FROM Proof_Policy, Proof_Fax
    WHERE Proof_Policy.PolicyID = Proof_Fax.ProofID AND Proof_Fax.IsCompleted = 1)
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('UPDATE Proof_Policy IsImaged True ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Set assignment info in Proof_Policy table
    UPDATE Proof_Policy
    SET Proof_Policy.AssignedAgent = Proof_Assigned.assignedto, Proof_Policy.DateTimeAssigned = Proof_Assigned.datetimeassigned
    FROM Proof_Policy, Proof_Assigned
    WHERE Proof_Policy.PolicyID = Proof_Assigned.ProofID
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('UPDATE Proof_Policy ERROR',16,1)
    GOTO _ROLLBACK
    END

    -- Remove Fax info and Submitter Info from Proof_Policy Table
    ALTER TABLE Proof_Policy DROP
    Fax1,
    Fax2,
    Fax3,
    Attn,
    SubmitterEmail;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('ALTER TABLE Proof_Policy DROP ERROR',16,1);
    GOTO _ROLLBACK;
    END

    -- Drop the Proof_Assigned Table
    DROP TABLE Proof_Assigned
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('DROP TABLE Proof_Assigned ERROR',16,1)
    GOTO _ROLLBACK
    END

    IF @@TRANCOUNT <> 0
    BEGIN
    COMMIT
    END

    _ROLLBACK:
    IF @@TRANCOUNT <> 0
    BEGIN
    ROLLBACK
    GOTO _DONE
    END

    _DONE:
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Stored procedures are checked agains the schema that exists when they are created. When you add columns to an existing table within the procedure, SQL Server doesn't know about those columns until the procedure runs. This makes references to the added columns invalid.

    There are a number of work arounds for this problem, but the best one is usually to alter the schema (table) first and then create new procedures to manipulate that schema. Making changes like this "on the fly" have a number of obscure consequences and are best avoided unless there is a REALLY compelling reason to support them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    What do you mean by alter schema first? Do I do that programmatically or by hand?

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Use a wrapper:

    Create usp_alter_tables sp and do those task then call ConvertProofTables sp.

  5. #5
    Join Date
    Sep 2012
    Posts
    4
    I will modify my SP to do as you suggested. I don't understand why the CREATE TABLE followed by the INSERT INTO works but the ALTER TABLE followed by the UPDATE doesn't.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dyarosh View Post
    I will modify my SP to do as you suggested. I don't understand why the CREATE TABLE followed by the INSERT INTO works but the ALTER TABLE followed by the UPDATE doesn't.
    I can replicate your error with code snippet below:

    -- Add new fields to Proof_Policy
    create table Proof_Agent (
    AgentID int not null)

    create table Proof_Policy (
    PolicyID int not null)

    ALTER TABLE Proof_Policy ADD
    AssignedAgent INT NULL,
    DateTimeAssigned DATETIME NULL,
    IsImaged BIT;
    IF @@ERROR <> 0
    BEGIN
    RAISERROR('ALTER TABLE Proof_Policy ERROR',16,1)
    GOTO _ROLLBACK
    END

    insert into Proof_Agent
    select 1234,1

    insert into Proof_Policy
    select 1234,1,GETDATE(),0

    update Proof_Policy
    set Proof_Policy.AssignedAgent = 2313
    from Proof_Policy,Proof_Agent
    where Proof_Policy.AgentID = Proof_Agent.AgentID

    select * from Proof_Policy
    The agentid in proof_policy doesn't exist in cach so if you alter table first in different sp then create the sp for ConvertProofTables then engine has a fresh copy cached. I'd do a recompile on the ConvertProofTables sp when executed.

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    I did what you suggested and put the creating of the tables and new fields in one Stored Procedure and the populating of the fields and table in another and everything worked fine. Thanks.

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    You're welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •