February 17, 2011 at 8:21 am
I am getting the follwing error even after it seems ok. Please any one can suggest me the solution for this.
Changed database context to 'MYDB'.
Msg 207, Level 16, State 1, Server AUTHENTIC\COM, Line 53
Invalid column name 'COLUMN2'.
This is the Script.
USE MYDB
GO
if
not exists (select * from information_schema.columns where
table_name='MYTABLE' and column_name='CLOUMN1')
BEGIN
ALTER TABLE [MYTABLE]
ADD
[COLUMN1] [int] NULL
END
If
exists (select * from information_schema.columns where
table_name='MYTABLE' and column_name='COLUMN1')
BEGIN
UPDATE MYTABLE
SET COLUMN1 = -2
ALTER TABLE MYTABLE
ALTER COLUMN COLUMN1 [int] NOT NULL
END
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DF_MYTABLE_COLUMN1]')
AND OBJECTPROPERTY(id, N'IsDefault') = -2)
BEGIN
ALTER TABLE dbo.MYTABLE
ADD CONSTRAINT [DF_MYTABLE_COLUMN1] DEFAULT (-2) FOR [COLUMN1]
END
if
not exists (select * from information_schema.columns where
table_name='MYTABLE' and column_name='COLUMN2')
BEGIN
ALTER TABLE [MYTABLE]
ADD
[COLUMN2] [int] NULL
END
If
exists (select * from information_schema.columns where
table_name='MYTABLE' and column_name='COLUMN2')
BEGIN
UPDATE MYTABLE
SET COLUMN2 = -2
ALTER TABLE MYTABLE
ALTER COLUMN COLUMN2 [int] NOT NULL
END
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DF_MYTABLE_COLUMN2]')
AND OBJECTPROPERTY(id, N'IsDefault') = -2)
BEGIN
ALTER TABLE dbo.MYTABLE
ADD CONSTRAINT [DF_MYTABLE_COLUMN2] DEFAULT (-2) FOR [COLUMN2]
END
February 17, 2011 at 9:20 am
I created this test table so your script could run in my test DB:
CREATE TABLE dbo.MYTABLE (id INT) ;
After that your script executed without error in SSMS. I am using SQL Server 2008 R2 Express Edition.
I did not run into the issue myself because I ran it into a clean environment but you do have COLUMN1 misspelled (find CLOUMN1) in your first IF check:
IF NOT EXISTS ( SELECT *
FROM information_schema.columns
WHERE table_name = 'MYTABLE'
AND column_name = 'CLOUMN1' )
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 17, 2011 at 9:37 am
Thank You.
I have Corrected that Column1 . But still facing the same error. I tried to add the columns in different tables still facing the same issue.
February 17, 2011 at 10:41 am
Please post the DDL describing the schema in place when you run your script. As I mentioned I am not receiving any errors running your code with a basic version of MYTABLE in place. My guess is that you have something else going on causing the error.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply