使用 SMO 脚本程序生成脚本时如何编写程序脚本? [英] How do I script procedures when generating a script with the SMO scripter?
问题描述
我正在尝试编写一个 Powershell 脚本来生成一个 SQL 脚本,该脚本在我的数据库中创建存储过程.这是我所拥有的:
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)
失败并出现错误:
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
查看输出文件,我看到它开始时确实有我的存储过程,这很棒,但是它有一堆系统过程.最后一个是sys.sp_fulltext_table
.
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
.
因为我已经指定了 $scrp.Options.AllowSystemObjects = $False
我不明白为什么要包含这些系统过程.我想去掉系统程序和错误信息.
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.
推荐答案
或者你可以完全跳过数组而直接使用管道.(来自我经常使用但修改过的东西,你使用你的变量)
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($_) }}
这篇关于使用 SMO 脚本程序生成脚本时如何编写程序脚本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!