Why can't I script out CLR assemblies with SMO in Powershell?

Question

I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most part my script works well and has helped identify a few gotchas in some old procs and functions, but I cannot get it to script out CLR functions.

There are only one or two and if I right click on them in SSMS and create script to new query editor window it works fine and I can then run that on the destination DB and it creates the assembly, but when I try and use SMO in Powershell it just does not script the assemblies (it doesn't error either). In essence, this is my script (left out the bits which create the connections/DB objects etc):

Write-Host "Getting DB objects..."
$assemblies = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } 
# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;
#Exclude GOs after every line
$scripter.Options.NoCommandTerminator = $false;
$scripter.Options.ToFileOnly = $false
$scripter.Options.AllowSystemObjects = $false
$scripter.Options.Permissions = $true
#$scripter.Options.DriAllConstraints = $true
$scripter.Options.DriForeignKeys = $false
$scripter.Options.SchemaQualify = $true
$scripter.Options.AnsiFile = $true
$scripter.Options.Indexes = $true
$scripter.Options.DriIndexes = $true
$scripter.Options.DriClustered = $true
$scripter.Options.DriNonClustered = $true
$scripter.Options.NonClusteredIndexes = $true
$scripter.Options.ClusteredIndexes = $true
$scripter.Options.FullTextIndexes = $true
$scripter.Options.EnforceScriptingOptions = $true
function CopyObjectsToDestination($objects) {
 foreach ($o in $objects) { 
 if ($o -ne $null) {
 try {
 Write-Host "Writing " $o.Name
 $script = $scripter.Script($o)
 $destDb.ExecuteNonQuery($script)
 } catch {
 #Make sure any errors are logged by the SQL job.
 $ex = $_.Exception
 $message = $ex.message
 $ex = $ex.InnerException
 while ($ex.InnerException) {
 $message += "`n$ex.InnerException.message"
 $ex = $ex.InnerException
 }
 Write-Error $message
 }
 }
 }
}
# Output the scripts
Write-Host "Create assemblies in destination database..."
CopyObjectsToDestination $assemblies

I feel like I'm missing something simple and obvious because I can get tables, procs, functions, views and the like without any problems at all. What have I missed?

• Ed Harper: Perhaps this is related to a specific combination of Powershell/SMO/SQL Server versions? This seems to be working for me on PS3 & SQL Server 2008 R2 SP2
• Steve Pettifer: Really? OK, I'll see what versions this is using on my machine. I have SQL 2008 R2 and 2012 installed but there's likely to be 2005 and 2008 components hanging about as it was someone else's before me. I'll try specifying a specific SMO version first.
• Ed Harper: Are the assemblies definitely in $schema? Does running $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } produce output?
• Ed Harper: I think the schema filter is the issue. The SqlAssembly class doesn't have a schema property - it has owner instead - technet.microsoft.com/en-us/library/…
• Steve Pettifer: Gah!! Of course! Assemblies don't live in schemas. I knew it had to be something simple - the copy and paste monster was at work. Stick that as an answer and I'll accept it because it's spot on, thank you.

Answer 1

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results:

$assemblies = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } 

I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.

• Steve Pettifer: Thank you! I changed the filter to -not $_.IsSystemObject to ensure I only pick up user-created assemblies and it works as expected. Been driving me nuts all day! Annoyingly I must have looked at that BOL page a dozen times today and didn't twig.