Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 –  Ed Harper Apr 28 at 14:39
    
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. –  Steve Pettifer Apr 28 at 14:43
    
Are the assemblies definitely in $schema? Does running $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } produce output? –  Ed Harper Apr 28 at 14:45
    
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/… –  Ed Harper Apr 28 at 14:50
    
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. –  Steve Pettifer Apr 28 at 14:52
add comment

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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. –  Steve Pettifer Apr 28 at 14:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.