0

Using a Powershell. I'm calling stored procedure containing batch of insert statements. Now I would like to capture the event log of the insert statements (both success message and error) into file like.

(1 row(s) affected).
Msg 8146, Level 16, State 1, Procedure test, Line 0 [Batch Start Line 0]
Procedure test has no parameters and arguments were supplied.

I'm trying this, but it not work. The execution happens, but file loads empty.

$conn = New-Object System.Data.SqlClient.SqlConnection  SqlConnection
$conn.ConnectionString = "Server=$server;Database=$databaseName;User=$userName;password=$password;trusted_connection=true;" # set the connection string
$conn.Open();
$cmd = $conn.CreateCommand(); 

Invoke-Sqlcmd -ServerInstance $server -Database $databaseName -Query "exec batch_insert_statements" -Verbose 
out-file -filepath C:\Logs\Test.txt
Idea
  • 19
  • 7
  • What you mean event log? SP's output a lot of stuff but they don't output an event log. Whats the issue with piping stuff to `Test.txt`? – Nick.Mc Jan 23 '18 at 05:50
  • I would like to capture the event log displayed on the message tab(both success message and failure error), like '(1 row(s) affected). Msg 8146, Level 16, State 1, Procedure test, Line 0 [Batch Start Line 0] Procedure test has no parameters and arguments were supplied.' The output file comes out to be empty – Idea Jan 23 '18 at 15:21
  • Possible duplicate of [SQL Server 2008: how to get the "Messages" tab (in Management Studio) output with powershell](https://stackoverflow.com/questions/6017475/sql-server-2008-how-to-get-the-messages-tab-in-management-studio-output-wit) – Nick.Mc Jan 23 '18 at 23:55
  • I think this is your answer https://stackoverflow.com/questions/4511498/powershell-invoke-sqlcmd-capture-verbose-output. I think this should be closed as a duplicate (but I can't do it because I already used a close vote on the wrong link) – Nick.Mc Jan 23 '18 at 23:56
  • This is different case from the articles shared. In this case the stored proc has DML statements which have event logs generated. Those are to be captured. Print command is being captured but this is not captured. Eg: (1 row(s) affected). – Idea Jan 24 '18 at 02:40

3 Answers3

0

You shouldn't need the first few lines, you also haven't piped the command to Out-File, try this:

$server = "."
$databaseName = "dbName"    
Invoke-Sqlcmd -ServerInstance $server -Database $databaseName -Query "exec batch_insert_statements" -Verbose -OutputSqlErrors 1 -IncludeSqlUserErrors | out-file -filepath C:\Logs\Test.txt
Owain Esau
  • 1,876
  • 2
  • 21
  • 34
  • I have tried this, but it gives me expections: NullReferenceExpection, and PipeLineStopped for Invoke-Sqlcmd. When the incorrect exec command in sql statement is used, it then gives SqlPowerShellSqlExecutionException. Both the cases the output file comes empty. My target is to capture both error and success message (event log) in the file – Idea Jan 23 '18 at 15:39
  • What version of PowerShell are you using? this runs fine for me (V5) – Owain Esau Jan 23 '18 at 21:28
  • Mine is also the same version: version: 5.1.15063.786 name: Windows PowerShell ISE Host – Idea Jan 23 '18 at 22:14
0

I found the answer here: https://www.sqlservercentral.com/Forums/Topic914307-391-1.aspx

$sql = "sqlcmd.exe -S ServerName -E -d DatabaseName -Q ""EXEC YourProc"""
Invoke-Expression $sql | Out-File C:\temp\outfile.txt

I just tried it (ps version 5.1.16299.98) and it wrote (1 rows affected) to outfile.txt

This would of course require SQLCMD.EXE to be installed, but I think that's a given if you are already using Invoke-Sqlcmd

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0

I found much similar way, but it is totally powershell.

$Query = 'EXEC tes'
$log = "c:\logs\Lead_Scoring_Log_$(get-date -f dd-MM-yyyy-HH-mm-ss).txt"
$SqlcmdOptions = @"
-S"$ServerInstance" -d "$Database" -v User=$userName Password=$password -Q 
"$Query"
"@
Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
$SqlcmdOptions
"@ -Wait -NoNewWindow -RedirectStandardOutput $log -PassThru
Idea
  • 19
  • 7