maybe those columns already exist...

Solved

Posted on 2012-09-19

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 Invalide column name error. 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

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 Invalide column name error. 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('ConvertProofTab

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

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

4 Comments

SET Proof_Policy.AssignedAgent

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.

http://www.dbforums.com/mi

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Group by correlation | 4 | 41 | |

SQL 2000: Shrink LDF file | 10 | 26 | |

Need help with a query | 3 | 35 | |

How to import SQL 2000 database to SQL 2014 | 5 | 6 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**17** Experts available now in Live!