SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Trouble Altering table using SQL-SMO in C#.Net
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 05/29/2009 :  11:21:45  Show Profile  Reply with Quote
Hi all,

I am faily new to SQL-SMO, and am using it for the first time in a C# application I am currently working on. Been following some online examples, and getting things to work for the most part. I ran into a problem though when trying to ALTER a table (i.e. simply adding a column to an existing table).

The database is SQL Server 2005 Express Edition running on my local machine.

The relavent code below is where the error is being thrown. I basically ran the same code before to add the table to the database (except using table.Create() instead of Alter()), so I know the database and table I'm trying to add the column too does exist, and I have the priviledge to connect to it.

Server server = new Server(conn);
Database db = server.Databases[ddlDatabaseList.Text];
Table table = new Table(db, "TestTable");

// Add Column
Column authorColumn = new Column(table, "Author");
authorColumn.DataType = DataType.VarChar(30);
authorColumn.Nullable = true;

// Add Column to Table Object

// Physically alter the table in the database

The error is happening on the "table.Alter()" line.

The error message is: "Alter failed for Table 'dbo.TestTable'. "

When I dig deeper into the error being produced, I see that the InnerException message is: "You cannot perform operation Alter on an object in state Creating."
What exactly does "object in state Creating" mean?

Below is the stack trace:

Microsoft.SqlServer.Management.Smo.FailedOperationException was unhandled
Message="Alter failed for Table 'dbo.TestTable'. "
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
at Microsoft.SqlServer.Management.Smo.Table.Alter()
at Powis_Database_Updater.Forms.MainForm.btnTestButton_Click(Object sender, EventArgs e) in C:\Documents and Settings\*****\Desktop\Main\Applications\***** Database Updater\***** Database Updater\Forms\MainForm.cs:line 340
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at Powis_Database_Updater.Program.Main() in C:\Documents and Settings\*****\Desktop\Main\Applications\***** Database Updater\***** Database Updater\Program.cs:line 17
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Pete Davis
Software Engineer

Flowing Fount of Yak Knowledge

1837 Posts

Posted - 05/30/2009 :  02:15:05  Show Profile  Reply with Quote
When adding a new table you need to call the Create method not the Alter method.
There is a full example here
Go to Top of Page

Starting Member

2 Posts

Posted - 06/01/2009 :  15:50:25  Show Profile  Reply with Quote
Quotes from original post:

"I ran into a problem though when trying to ALTER a table (i.e. simply adding a column to an existing table)."


"I basically ran the same code before to add the table to the database (except using table.Create() instead of Alter())..."

I am not trying to create a table, I am trying to alter a table I already created.
I actually was following the example from the website you posted, and from examples on MSDN, when I ran into the ALTER problem.

I did however discover the solution.

The following line:

Table newTable = new Table(db, "TestTable");

Needed to be changed into:

Table table = db.Tables["TestTable"];

Pete Davis
Software Engineer
Go to Top of Page

Flowing Fount of Yak Knowledge

1837 Posts

Posted - 06/01/2009 :  20:11:00  Show Profile  Reply with Quote
I should have read your comments not your code - I saw the
Table table = new Table(db, "TestTable");
and immediately thought you wanted to create a new table.
Go to Top of Page

Starting Member

6 Posts

Posted - 07/23/2013 :  17:53:15  Show Profile  Reply with Quote
just in case this helps out somebody 4 years later...

I was doing nearly the same thing. I had a brainfart and didn't think about doing db.Tables["mytablename"]. Instead, I created a newTable as you did originally, but before I attempted to add the new column, I had:


Which loads the current schema into the table object.

"If we can put it into a table, we can kill it"
-Ahnold Sequelnator
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000