从Powershell调用存储过程并将输出存储到文件中

Question

我正在尝试在远程服务器上调用一个存储过程（Ola的维护脚本！）（代码的这部分是有效的），并且SP的输出是DBCC CHECKDB的结果（因此它在消息选项卡中）。

我试图编写一些代码将此消息输出捕获到远程服务器上的文件中，但该文件未被创建，尽管SP已成功完成。

$OutputFile = "\\XXX\E$\SQLAdmin\DatabaseCheckDB\ScriptOutput\ScriptOutput.txt"

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Out-File -filepath $OutputFile -inputobject $event.Message };

 $SqlConnection.add_InfoMessage($handler); 
 $SqlConnection.FireInfoMessageEventOnUserErrors = $true;
 $SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")
 $SqlConnection.Open() | Out-Null

$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.DatabaseIntegrityCheck", $SqlConnection)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'

$cmd.Parameters.Add("@Databases","ALL_DATABASES") | Out-Null

$cmd.ExecuteNonQuery() | Out-Null

$SqlConnection.Close()

有人能看出我在这里做错了什么吗？谢谢您的帮助！

- dnaman

---

.ExecuteNonQuery() 仍然允许信息消息返回吗？ - Aaron Bertrand

@Aaron，根据我所读的，将@cmd.ExecuteNonQuery()导向NULL可以抑制该函数的信息，但该函数的输出仅告诉调用SP返回了多少行。在这种情况下，它将返回-1，因为在成功调用Ola脚本时没有返回任何行。但我希望将消息输出到文本文件中，以便我可以将其作为检查数据库是否干净的证明。 - dnaman

无论您将cmd.ExecuteNonQuery()放在管道的哪个位置，它都会抑制结果集的返回 - 所以我的问题是，当您使用ExecuteNonQuery()时，它是否也可能抑制消息？我不知道，因此提出了这个问题。 - Aaron Bertrand

@AaronBertrand 在下面回答，这样就不会压制消息了，我需要在创建 sqlconnection 后将处理程序放在正确的位置。 - dnaman

Answer 1

您是否已安装了SQL Powershell模块（sqlps）？如果是的话，您可以使用它，并将Verbose流（其中包含来自SQL的打印消息）导出到文件中。

Invoke-Sqlcmd -Query 'DBCC CHECKDB' `
    -ServerInstance '(local)' `
    -Database 'tempdb' `
    -Verbose 4>&1 |
        Out-File c:\temp\test.txt

如果这不是一个选项，我认为在您的原始代码中发现了问题 - 您连接了InfoMessage事件，但随后创建了全新的SqlConnection。这个新的SqlConnection没有事件处理程序，因此不会响应任何打印消息。

尝试替换：

$SqlConnection.add_InfoMessage($handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")

使用

$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")
$SqlConnection.add_InfoMessage($handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;

- Alex

---

太好了，就是这样，新建连接的顺序和连接 InfoMessage 事件。在定义输出文件时，我还必须加上“-append”，以便不会不断地覆盖文件内容。 - dnaman