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

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

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…

[pre] $grandchildren += ($table.Checks | foreach{ $_.ToString() })[/pre]

Thank you.

 

 

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

Too many `foreach` blocks…

Try this…

[pre]
foreach ($item in $_.ExtendedProperties.GetEnumerator())
{
if ($item)
{
$item.Script()
}
}
[/pre]

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

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

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

Hope this works!

Thank you.

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

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/