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
$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.
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…
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.
$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]