MS-SQL Error on removing a column using SMO

Asked By Linda Stewart 0 points N/A Posted on - 09/30/2011

Hi guys,

I am trying to figure out why I can add a column to a table using SMO, but when I try and remove a column, I get an error which indicates the following:

You cannot perform operation Remove on an object in state Existing. (Microsoft.SqlServer.Smo)

That makes no sense. Surely when you remove a column, the state is Existing.

The code used to add and remove is as follows:

myTable.Columns.Add(myColumn)

myTable.Columns.Remove(myColumn)

I did a little searching on this but could not get any solution. Does anybody have some clues?

Waiting for reply.

• Status: Open
• Question Views: 2275
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Database

Answered By stephen1 10 points N/A #83918

MS-SQL Error on removing a column using SMO

Hi Linda,

• To add a column to your existing table in the database you can try this option.

ALTER TABLE <table_name>
      ADD <column_name> <data_type> <null_attribute>

For e.g.

ALTER TABLE student

      ADD column1   NOT NULL WITH DEFAULT
      ADD admission_date DATE
      ADD class CHAR(3)

•  ALTER TABLE <table_name>
        ALTER COLUMN <column_name>
        <modification_type>

For e.g

especially for deleting a column you should try drop column.

 The conditions are the table should not be typed table.

And the table should not have data capture enabled.

DROP COLUMN column_name

Hope, This helped you.

About stephen1

Questions
1

Answers
7

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 10/17/2011
• Question Category: Database