Saving the output of a stored procedure to a file after calling it from Powershell
I am trying to call SP (serving an Ola script!) On a remote server (this part of the code works) and the SP output is the result of DBCC CHECKDB (which is why it's in the Message tab).
I tried to put together some code to capture this output of messages to a file on the remote server, but no file is created, although the SP exits fine.
$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()
Can anyone see what I am doing wrong here? Thank you in advance!
source to share
Do you have the SQL Powershell (sqlps) module installed? If so, you can use that and pipe the output from the Verbose stream (which contains printed messages from SQL) to your file.
Invoke-Sqlcmd -Query 'DBCC CHECKDB' `
-ServerInstance '(local)' `
-Database 'tempdb' `
-Verbose 4>&1 |
Out-File c:\temp\test.txt
If that's not an option, then I think I noticed a problem in your original code - you hook up the InfoMessage event, but then start creating a new SqlConnection. This new SqlConnection does not have an event handler on it and therefore will not respond to any printed messages.
Try to replace
$SqlConnection.add_InfoMessage($handler);
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")
from
$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")
$SqlConnection.add_InfoMessage($handler);
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
source to share