HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
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

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#64073, answer score: 4

Revisions (0)

No revisions yet.