Eventually unable to add more columns to a table in a MS SQL Server database.

Posted on 2010-09-15
Last Modified: 2012-05-10
I am using the following code to add columns to a table (Server Management Objects library):

	Column newColumn = new Column(tbl, name, DataType.Bit);
	newColumn.Nullable = false;
	newColumn.DefaultConstraint.Text = "0";

Open in new window

This works just fine for a while, but without fail, I am eventually no longer able to add columns.  I get the error, "Alter failed for Table 'dbo.myTable'."  (The InnerException.Message is "An exception occurred while executing a Transact-SQL statement or batch.")

I'm having a devil of a time trying to pinpoint the source of this problem.  It might happen after I've actually edited some of the cells in the table, but I'm not sure.

Note that column rename and remove operations continue to work with no apparent problems.  It's just adding columns that fails.

I'm hoping that someone out there with experience with this exception can help me out.
Question by:MiloDCooper
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
LVL 29

Expert Comment

ID: 33688607
you may have some sort of lock on your table that is prohibiting it from being altered.
Try to re-run the statement after a period of time and see if it works then

Author Comment

ID: 33688677
Upon further investigation, the error I'm getting is this:

"There is already an object named 'DF_myTable_NEW_COLUMN' in the database. Could not create constraint."

Accepted Solution

MiloDCooper earned 0 total points
ID: 33688774

newColumn.AddDefaultConstraint("DF_" + name);

That gets rid of the problem, but something tells me that is not a permanent solution.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Expert Comment

by:Gururaj Badam
ID: 33689173
The problem could be newColumn.AddDefaultConstraint(); may be generating a Constraint Name which is clashing with the existing name. It's good to specify a property constraint name on your behalf to avoid such cases.

Expert Comment

ID: 33689379
I am just curious why do you use .net code to add columns to a table.
As I think this is not a very good practice.

Expert Comment

ID: 33689590
Normally, you pass data into database by .net apps, let database uses stored procedure to handle the data process.
It is very rare to see others using .net code to add column to a table.
I think database needs to be properly designed before start  to do coding.

Normally, the task for .net code should be passed in and out the info data, not create a new database table column.

What happen if the table is locked, and you cannot update the table structure?
The info from .net form will be lost.

You can have different ways to store the data.

If you are planing to write an application, my suggestion will be prepare database first,
then start write some code.

Any appilcations always can be  separated two parts, data and function.

And data is the key.

have fun

Author Comment

ID: 33692476
@novice_novice: Can you elaborate, please?  Is newColumn.AddDefaultConstraint("DF_" + name) enough to do the trick?  If so, then how can I be sure that executing that command again won't cause problems when the value of name is the same as it was in a previous operation?

@timexist: I spent three days setting up the server and designing the database and table.  I'm using .NET code because I'm adding SQL database functionality to an existing application.  This application is used chiefly by personnel who've had no experience with databases, so I'm presenting them with an accessible UI that gives them the ability to add, remove, and rename columns (which will be a normal part of their workflow).

Expert Comment

by:Gururaj Badam
ID: 33692650
If you're adding constraint on a column it should only be once not again unless it's a different validation. Database expects names to be unique irrespective of whether it's associated with same Table or different.

Author Comment

ID: 33695863
@Novice_Novice: The thing is, the objects that the database was telling me already existed were for columns that I had *removed* earlier.

What worries me is that my users will remove a column, then later add a column with the same name (hey, I have to cover every possibility), whereupon the database will whine that "there is already an object named blah blah blah."

When I remove a column (i.e. tbl.Columns[name].Remove()), is there some extra clean-up or refresh that I should be doing?

Author Comment

ID: 33695875
Sorry, I meant tbl.Columns[name].Drop(), not tbl.Columns[name].Remove().

Expert Comment

by:Gururaj Badam
ID: 33698514
I'm expecting your code looks like this


If so, the Alter method should have updated the table to remove the specified column.

Author Comment

ID: 33704897
@ Novice_Novice: Yeah, that's my code.  just Drop() then Alter().

Everything seems to work OK since I supplied newColumn.AddDefaultConstraint() with a string, but I'd really love to know why not doing so confounded my table.  That's the only part of my code that changed.  It doesn't make sense to me.  Why would using the parameter-less version of AddDefaultConstraint() introduce vestigial data from previously dropped columns into my table??

Expert Comment

by:Gururaj Badam
ID: 33707172
OK here's the catch . I think you will have to remove the constraints first before dropping the table.

What i'll advise you is try to remove the column by writing SQL statement and see whether the order matches with that of your code in c#

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

635 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question