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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.05 seconds. Powered By: Snitz Forums 2000