SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

P1ST0LPETE
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
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
Flowing Fount of Yak Knowledge

USA
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
http://www.davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx
Go to Top of Page

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

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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
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

xyvyx
Starting Member

USA
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:

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
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.09 seconds. Powered By: Snitz Forums 2000