Trouble Altering table using SQL-SMO in C#.Net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Trouble Altering table using SQL-SMO in C#.Net

Author

Topic

P1ST0LPETE
Starting Member

2 Posts

Posted - 2009-05-29 : 11:21:45

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
table.Columns.Add(authorColumn);

// Physically alter the table in the database
table.Alter();

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
HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Table&LinkId=20476"
Message="Alter failed for Table 'dbo.TestTable'. "
Source="Microsoft.SqlServer.Smo"
Operation="Alter"
StackTrace:
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
pdavis@powiscorp.com

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 02:15:05

When adding a new table you need to call the Create method not the Alter method.
There is a full example here
http://www.davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

P1ST0LPETE
Starting Member

2 Posts

Posted - 2009-06-01 : 15:50:25

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)."

And

"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
pdavis@powiscorp.com

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-01 : 20:11:00

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.

xyvyx
Starting Member

6 Posts

Posted - 2013-07-23 : 17:53:15

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:

newTable.Refresh();

Which loads the current schema into the table object.

"If we can put it into a table, we can kill it"
-Ahnold Sequelnator

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources