Sql Server 脚本数据:输出到文件时 SMO.Scripter 不起作用
当我在底部运行 Powershell 脚本时,我收到此错误消息:
Exception calling "EnumScript" with "1" argument(s): "Script failed for Table 'dbo.Product'. "
At :line:48 char:35
+ foreach ($s in $scripter.EnumScript <<<< ($tbl)) { write-host $s }
但是,当我注释掉 output_file 行
#$output_file="C:\Product.sql"
(不会将 Scripter 选项设置为写入文件)时,它工作正常并将 INSERT 语句输出到控制台。
这是失败的脚本,是我遗漏了什么吗?
# Script INSERTs for given table
param
(
[string] $server,
[string] $database,
[string] $schema,
[string] $table,
[string] $output_file
)
$server="devdidb02"
$database="EPCTrunk_EPC"
$schema="dbo"
$table="Product"
$output_file="C:\Product.sql"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
# Get the database and table objects
$db = $srv.Databases[$database]
$tbl = $db.tables | Where-object {$_.schema -eq $schema-and$_.name -eq $table}
# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema = $false;
$scripter.Options.ScriptData = $true;
#Exclude GOs after every line
$scripter.Options.NoCommandTerminator = $true;
if ($output_file -gt "")
{
$scripter.Options.FileName = $output_file
$scripter.Options.ToFileOnly = $true
}
# Output the script
foreach ($s in $scripter.EnumScript($tbl)) { write-host $s }
I get this error message when I run the Powershell script at the bottom:
Exception calling "EnumScript" with "1" argument(s): "Script failed for Table 'dbo.Product'. "
At :line:48 char:35
+ foreach ($s in $scripter.EnumScript <<<< ($tbl)) { write-host $s }
However, when I comment out the output_file line
#$output_file="C:\Product.sql"
(which won't set the Scripter options to write to file), it works fine and outputs the INSERT statments to the console.
Here's the failing script, is there something I'm missing?
# Script INSERTs for given table
param
(
[string] $server,
[string] $database,
[string] $schema,
[string] $table,
[string] $output_file
)
$server="devdidb02"
$database="EPCTrunk_EPC"
$schema="dbo"
$table="Product"
$output_file="C:\Product.sql"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
# Get the database and table objects
$db = $srv.Databases[$database]
$tbl = $db.tables | Where-object {$_.schema -eq $schema-and$_.name -eq $table}
# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema = $false;
$scripter.Options.ScriptData = $true;
#Exclude GOs after every line
$scripter.Options.NoCommandTerminator = $true;
if ($output_file -gt "")
{
$scripter.Options.FileName = $output_file
$scripter.Options.ToFileOnly = $true
}
# Output the script
foreach ($s in $scripter.EnumScript($tbl)) { write-host $s }
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我运行了你和基思的,看起来问题出在你为文件设置的路径中。我能够重现您的错误。您使用的是
$output_file="C:\Product.sql"
。然后我将路径更改为:$output_file="$home\Product.sql"
它运行得很好并给了我文件。我猜测这是因为我没有写入 c:\ 的权限,这可能是您遇到的问题。
顺便说一句 - 在这种情况下,我的主目录是我登录的用户文件夹,所以我能够在那里找到它。
I ran both yours and Keith's and it looks like the issue is in the path you are setting for the file. I was able to reproduce your error. You were using
$output_file="C:\Product.sql"
. Then I changed the path to:$output_file="$home\Product.sql"
it ran just fine and gave me the file.I am guessing that the reason for this is that I don't have permission to write to c:\ which may be the problem you are having.
BTW - my home dir in this case for me is my user folder for my login so I was able to find it there.
FWIW 我无法重现您使用 AdventureWorks DB 看到的错误。以下生成 foo.sql 文件,没有任何错误:
FWIW I'm not able to repro the error you see using the AdventureWorks DB. The following generates the foo.sql file without any errors: