SMO - SQL Server Management Objects

• Rex Mollentze

  SSC Veteran

  Points: 286

  More actions

  September 19, 2006 at 7:25 pm

  #169813

  Hi,

   

  I am trying tio figure out why I can add a column to a table using SMO, but when I try and remove a column, I get an error which seems to indicate that you cannot perform a Remove on object in a state  Existing. That makes no sense. Surely when you remove a column, the state is Existing. The code used to add and remove is a as follows:

  myTable.Columns.Add(myColumn)

  myTable.Columns.Remove(myColumn)

  The add works fine, but the remove gets the error below:

  Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException was unhandled

    HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtID=Remove&LinkId=20476"

    Message="You cannot perform operation Remove on an object in state Existing."

    ProductName="Microsoft SQL Server"

    Source="Microsoft.SqlServer.Smo"

    StackTrace:

         at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.RemoveObj(SqlSmoObject obj, ObjectKeyBase key)

         at Microsoft.SqlServer.Management.Smo.ColumnCollection.Remove(Column column)

         at MaintainColumns.Form1.btnRemove_Click(Object sender, EventArgs e) in C:\Documents and Settings\Rex.GARDENROUTE7\My Documents\Visual Studio 2005\Projects\MaintainColumns\Form1.vb:line 75

         at System.Windows.Forms.Control.OnClick(EventArgs e)

         at System.Windows.Forms.Button.OnClick(EventArgs e)

         at System.Windows.Forms.Button.PerformClick()

         at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)

         at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)

         at System.Windows.Forms.Control.PreProcessMessage(Message& msg)

         at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)

         at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)

         at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(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(ApplicationContext context)

         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

         at MaintainColumns.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

         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()

   

  Any help will be appreciated.

   

  Thanks,

   

  Rex

• cliffb

  SSCarpal Tunnel

  Points: 4598

  More actions

  September 21, 2006 at 1:35 pm

  #661848

  Try calling myTable.Alter() after adding the column. I have a feeling the Add is in some kind of pending state and is not really part of the table object until you alter the table explaining why you get no error for Add but do have an error on Remove.

   

• Rex Mollentze

  SSC Veteran

  Points: 286

  More actions

  September 21, 2006 at 1:57 pm

  #661856

  Cliff,

  Thanks for the response. It seems like doing the remove from the collection object does not work, but when you apply the drop of the column object on the table, it works fine. Strange...

  The add looks like this:

          ' Set the database

          Dim db As Database

          db = SQLServer.Databases("Mydatabase")

          ' Create Table object

          Dim myTable As Table

          myTable = db.Tables(Me.txtTableName.Text)

          ' Create a Column object, then populate it to define a column

          Dim myColumn As New Column(myTable, Me.txtFieldName.Text, DataType.VarCharMax)

          myColumn.Nullable = True

          ' Add the populated Column object to its containing collection.

          myTable.Columns.Add(myColumn)

          ' Create the column by committing the unit of change.

          myTable.Alter()

  =================================================

  The drop of the column like this:

          ' Get Table object

          Dim myTable As Table

          myTable = db.Tables(Me.txtTableName.Text)

          ' Create a Column object, then populate it to define a column

          Dim myColumn As Column

          myColumn = myTable.Columns(Me.txtFieldName.Text)

          ' Remove the populated Column object from its containing collection.

          myTable.Columns(myColumn.Name).Drop()

          ' Commit the unit of change.

          myTable.Alter()

   

  Thanks.