dyarosh
asked on
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 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 les','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.DateTimeAssig ned = Proof_Assigned.datetimeass igned
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
maybe those columns already exist...
ASKER
They don't exist before I ALTER the Table and create them. The error is on the line:
SET Proof_Policy.AssignedAgent = Proof_Assigned.assignedto, Proof_Policy.DateTimeAssig ned = Proof_Assigned.datetimeass igned
SET Proof_Policy.AssignedAgent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found the answer on a forum at the following link:
http://www.dbforums.com/microsoft-sql-server/1685324-invalid-column-name-error-sql-stored-procedure.html
http://www.dbforums.com/microsoft-sql-server/1685324-invalid-column-name-error-sql-stored-procedure.html