patternsqlMinor
Why can't I script out CLR assemblies with SMO in Powershell?
Viewed 0 times
scriptwhycanwithpowershellclrsmoassembliesout
Problem
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
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
Solution
The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results:
I'm not sure whether you need to use the
$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.Code Snippets
$assemblies = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema }Context
StackExchange Database Administrators Q#64073, answer score: 4
Revisions (0)
No revisions yet.