How do I script procedures when generating a script with the SMO scripter?

Question

I am trying to write a Powershell script to generate a SQL script that creates the stored procedures in my database. Here is what I have:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

Function to-array ($c)
{
    foreach($item in $c)
    {
      $result += @($item)
    }
    $result
}

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$dbs = $s.Databases
$fa = $dbs["FinancialAid"]

$scrp.Options.ScriptData = $True
$scrp.Options.AllowSystemObjects = $False
$scrp.Options.FileName = 'StoredProcedures.sql'
$scrp.Options.ToFileOnly = $True

# Finally the stored procedures

$sparray = to-array($fa.StoredProcedures)
$scrp.EnumScript($sparray)

This fails with the error:

Exception calling "EnumScript" with "1" argument(s): "Script failed for Server 'LOCALHOST'. "
At H:\Visual Studio 2012\FinancialAidApplication\Financial Aid Application\FinancialAidApplication\main\src\FinancialAidApp
\SQL-Scripts\sp.ps1:28 char:17
+ $scrp.EnumScript <<<< ($sparray)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Looking at the output file, I see that it does have my stored procedures at the beginning, which is great, but then it has a bunch of system procedures. The last one is sys.sp_fulltext_table.

As I already specified $scrp.Options.AllowSystemObjects = $False I don't understand why these system procedures are being included. I would like to get rid of the system procedures and the error message.

Bruce · Accepted Answer · 2013-10-15 17:11:37Z

1

Or you could just skip the array completely and just use the pipeline. (From a piece of something I use regularly but modified you use your variables)

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$s= new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'LOCALHOST'
$fa = $s.Databases['FinancialAid']
   $scrp = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter")
   $scrp.Server = $s
   $options = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
   $options.IncludeHeaders = $true
   $options.FileName = 'C:\temp\StoredProcedures.sql'
   $options.AppendToFile = $true
   $options.ToFileOnly = $true
   $options.ScriptBatchTerminator = $true
   $scrp.Options = $options
$fa.StoredProcedures | foreach {if ($_.IsSystemObject -eq $false) { $scrp.Script($_) }}

answered Oct 15, 2013 at 17:11

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Samuel Edwin Ward Over a year ago

One thing: I had to use $scrp.EnumScript because $scrp.Script refuses to script "data" (and I guess it considers procedure bodies data).

Bruce Over a year ago

@SamuelEdwinWard, are you saying you had to modify my example to get it to work? If so I'm confused. With a change of server and database name to the code above it worked without issue before I added it as an answer.

Samuel Edwin Ward Over a year ago

You're right, what you have should work. I had set ScriptData to true and I shouldn't have. It must have been a mistake I made while I was struggling with this earlier.

Ben Thul · Accepted Answer · 2013-10-15 16:02:14Z

1

A couple of things

Try to get to the actual exception. Throw "inner exception powershell" into your favorite search engine. Right now, it's just a generic "something went wrong", which is hard to diagnose (both for you and for us!).
Try $sparray = to-array($fa.StoredProcedures | where {$_.IsSystemObject -eq $false)

answered Oct 15, 2013 at 16:02

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

How do I script procedures when generating a script with the SMO scripter?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related