Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-15
13
Medium Priority
?
312 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

773 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