Solved

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

Posted on 2010-09-15
13
304 Views
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.AddDefaultConstraint();
	newColumn.DefaultConstraint.Text = "0";
	tbl.Columns.Add(newColumn);
	tbl.Alter();

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.
0
Comment
Question by:MiloDCooper
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 29

Expert Comment

by:QPR
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
0
 

Author Comment

by:MiloDCooper
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."
0
 

Accepted Solution

by:
MiloDCooper earned 0 total points
ID: 33688774
Hmmm.

newColumn.AddDefaultConstraint("DF_" + name);

That gets rid of the problem, but something tells me that is not a permanent solution.
0
 
LVL 8

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.
0
 
LVL 4

Expert Comment

by:timexist
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.
0
 
LVL 4

Expert Comment

by:timexist
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:MiloDCooper
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).
0
 
LVL 8

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.
0
 

Author Comment

by:MiloDCooper
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?
0
 

Author Comment

by:MiloDCooper
ID: 33695875
Sorry, I meant tbl.Columns[name].Drop(), not tbl.Columns[name].Remove().
0
 
LVL 8

Expert Comment

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

tbl.Columns[name].Drop();
tbl.Alter();

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

Author Comment

by:MiloDCooper
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??
0
 
LVL 8

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#
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now