Solved

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

Posted on 2010-09-15
13
308 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
[X]
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
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql views 3 57
async questions 5 50
Automate an Oracle update in Excel 7 70
SQL State HYT00. Timeout expired proplem 8 46
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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

710 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