SqlServer.Management.Smo.Column] does not contain a method named 'op_Addition'

Welcome Forums General PowerShell Q&A SqlServer.Management.Smo.Column] does not contain a method named 'op_Addition'

Viewing 7 reply threads
  • Author
    Posts
    • #193640
      Participant
      Topics: 10
      Replies: 21
      Points: 107
      Rank: Participant

      Hi, I am running a script where it uses the module sqlserver version 20, it generates an error regarding a method called 'op_Addition' which is not ion the script as follows

      Method invocation failed because [Microsoft.SqlServer.Management.Smo.Column] does not contain a method named 'op_Addition'.
      At line:36 char:13
      + $grandchildren += $table.Checks | foreach{ $_ }
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (op_Addition:String) [], RuntimeException
      + FullyQualifiedErrorId : MethodNotFound

      I upgraded to version 21.1.18218 of sql server module but getting the exact same error.. Here is the piece of script where the errors happen

      $srv = new-object "$Smo.Server"(new-object $Conn(new-object $sqlConn($connectionString)))
      }
      
      $schemae = @() #holds the schema collection
      $children = @() #holds the child objects of each schema.
      $database = $srv.Databases[$DatabaseName] #instantiate the database object
      $objectCollection = $database.schemas | Where-Object { $_.Owner -eq 'dbo' } -OutVariable +schemae |
      foreach{ $_.EnumOwnedObjects() } |
      Foreach{ $srv.GetSmoObject($_) } -OutVariable +children | Foreach{
      $GrandChildren = @() # columns, indexes, triggers, parameters etc
      $Object = $_;
      switch ($Object.urn.type)
      {
      'Table' { #add the table and the child objects that can have EPs
      $table = $Object
      $grandchildren = $table.Columns | foreach{ $_ } #get the columns
      $grandchildren += $table.Indexes | foreach{ $_ } #get the indexes
      $grandchildren += $table.Triggers | foreach{ $_ }#get the triggers
      $grandchildren += $table.Checks | foreach{ $_ }
      }
      'View' { #add the Views and the child objects that can have EPs
      $View = $Object
      $grandchildren = $View.Columns | foreach{ $_ } #get the columns
      $grandchildren += $View.Indexes | foreach{ $_ }#get the indexes
      $grandchildren += $View.Triggers | foreach{ $_ }#get the triggers
      }
      
      ......
      
      ......
      
      .......

      Where is this method and which object is calling it? I tried Debug, the script connects correctly and it gets the DB objects but not able to see when It calls this method.

      Thanks for your help

    • #193646
      Moderator
      Topics: 3
      Replies: 169
      Points: 891
      Helping Hand
      Rank: Major Contributor

      Not sure what exactly this script does, but seeing that error it is messed up with the data type conversion. If they are all type of string then you can use explicit string conversion using .ToString() method...

      You can use something like this...

       $grandchildren += ($table.Checks | foreach{ $_.ToString() })

      Thank you.

       

       

    • #193664
      Participant
      Topics: 10
      Replies: 21
      Points: 107
      Rank: Participant

      Thanks, I added the .ToString() and the initial error disappeared. For "Not sure what exactly this script does...", this script is to get Extended properties for all objects from SQL server database. Hereunder is the full script. Now I am getting an error on the last line as follows (it seems that thers a null value in the collection somewhere)

      You cannot call a method on a null-valued expression.
      At line:63 char:12
      + foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script() ...
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull

       

      The script

      $ServerName = 'SQLServer' #the full name of your server
      $DatabaseName='TestDB' #the name of your database
      $Credentials = 'integrated security=True' #your user-id and password if necessary
      
      $SMO = 'Microsoft.SqlServer.Management.SMO' # to shorten some of the devotions to libraries
      $Conn = 'Microsoft.SqlServer.Management.Common.ServerConnection'
      $SQLConn = 'System.Data.SqlClient.SqlConnection'
      $connectionString = "Data Source=$serverName;$credentials;pooling=False;multipleactiveresultsets=False;packet size=4096"
      #this might need to be changed under some circumstances 
      try #making the connection
      {
      $srv = new-object "$Smo.Server"(new-object $Conn(new-object $sqlConn($connectionString)))
      }
      catch #if it isn't going to happen
      {
      "Could not connect to SQL Server instance '$servername': $(
      $error[0].ToString() + $error[0].InvocationInfo.PositionMessage). The script is aborted"
      exit - 1 # return -1 if there is an error otherwise 0
      } # 
      
      $schemae = @() #holds the schema collection
      $children = @() #holds the child objects of each schema.
      $database = $srv.Databases[$DatabaseName] #instantiate the database object
      $objectCollection = $database.schemas | Where-Object { $_.Owner -eq 'dbo' } -OutVariable +schemae |
      foreach{ $_.EnumOwnedObjects() } |
      Foreach{ $srv.GetSmoObject($_) } -OutVariable +children | Foreach{
      $GrandChildren = @() # columns, indexes, triggers, parameters etc
      $Object = $_;
      switch ($Object.urn.type)
      {
      'Table' { #add the table and the child objects that can have EPs
      $table = $Object
      $grandchildren = $table.Columns | foreach{ $_.ToString() } #get the columns
      $grandchildren += $table.Indexes | foreach{ $_.ToString() } #get the indexes
      $grandchildren += $table.Triggers | foreach{ $_.ToString() }#get the triggers
      $grandchildren += $table.Checks | foreach{ $_.ToString() }
      }
      'View' { #add the Views and the child objects that can have EPs
      $View = $Object
      $grandchildren = $View.Columns | foreach{ $_.ToString() } #get the columns
      $grandchildren += $View.Indexes | foreach{ $_.ToString() }#get the indexes
      $grandchildren += $View.Triggers | foreach{ $_.ToString() }#get the triggers
      }
      'StoredProcedure' #add the SPs and the child objects that can have EPs
      {
      $grandchildren = $Object.Parameters | foreach{ $_.ToString() }#get the parameters
      }
      'UserDefinedFunction'
      {
      $UserDefinedFunction = $Object
      $grandchildren = $UserDefinedFunction.Columns | foreach{ $_.ToString() } #get the columns
      $grandchildren += $UserDefinedFunction.Checks | foreach{ $_.ToString() }
      $grandchildren = $UserDefinedFunction.Parameters | foreach{ $_.ToString() }#get the parameters
      }
      'Queue'
      { $queue = $Object.EventNotifications }#get the event notifications
      }$grandchildren #put all these columns, indexes, triggers, parameters etc in the collection
      }
      $objectCollection += $schemae #add these schemae to the collection of objects 
      $objectCollection += $children #and the schema-based objects (e.g. tables, functions views)
      $objectCollection += $database #and the database itself
      $TheBuildScript=$objectCollection | #now we just take all the extended properties and
      foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script()} #script them out

      so line 63, col 12 is the begining of $_.ExtendedProperties.GetEnumerator.

      I tried something like this but it did not work

      foreach {$_ } | `
      If (!$_)
      (
      foreach {$_.ExtendedProperties.GetEnumerator()} `
      |foreach{$_.Script()} `
      )
    • #193670
      Moderator
      Topics: 3
      Replies: 169
      Points: 891
      Helping Hand
      Rank: Major Contributor

      Too many `foreach` blocks...

      Try this...

      foreach ($item in $_.ExtendedProperties.GetEnumerator())
      {
      if ($item)
      {
      $item.Script()
      }
      }
      
    • #193679
      Participant
      Topics: 10
      Replies: 21
      Points: 107
      Rank: Participant

      Kiran, I needed to drop the parantheses around ($item in $_.ExtendedProperties.GetEnumerator()) as I was getting the broken red lines indicating

      "Unexpected token 'in'  in expression or statement

      another red line under the last indicating

      Unexpected token ')'....

      Error

      After droping them, Error disappeared, I re executed, getting the exact same error

      You cannot call a method on a null-valued expression.
      At line:65 char:1
      + $TheBuildScript=$objectCollection |`
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull

      if ($item)
      {
      $item.Script()
      }

      The error is happening I think before the if statement

    • #193718
      Moderator
      Topics: 3
      Replies: 169
      Points: 891
      Helping Hand
      Rank: Major Contributor

      Remove these two lines...

      $TheBuildScript=$objectCollection | #now we just take all the extended properties and
      foreach {$_.ExtendedProperties.GetEnumerator()}|foreach{$_.Script()} #script them out

      And replace with below...

      $TheBuildScript = foreach ($item in $objectCollection.ExtendedProperties.GetEnumerator())
      {
      if ($item)
      {
      $item.Script()
      }
      }
      

      Hope this works!

      Thank you.

    • #193793
      Participant
      Topics: 10
      Replies: 21
      Points: 107
      Rank: Participant

      Kiran, really appreciate your help. No it did not work, first, it was erroring on

      $objectCollection.ExtendedProperties.GetEnumerator()

      indicating that the object has no GetEnumerator() method.

      I modified your script as follows to mimic what is coded in the statement "foreach {$_.ExtendedProperties.GetEnumerator()}"

      $TheBuildScript = foreach ($item in $objectCollection)
      {
      if ($item)
      {
      $item.ExtendedProperties.GetEnumerator()
      #$item.Script()
      }
      }

      I get the same exact error.

      You cannot call a method on a null-valued expression.
      At line:72 char:1
      + $item.ExtendedProperties.GetEnumerator()
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull

    • #193796
      Participant
      Topics: 10
      Replies: 21
      Points: 107
      Rank: Participant

      If you like you can find the owner of the script who write the thread about extended properties at https://www.red-gate.com/simple-talk/sql/database-delivery/reading-writing-creating-sql-server-extended-properties/

       

Viewing 7 reply threads
  • You must be logged in to reply to this topic.