I am using the Hangfire.Console package (IdentityStream.Hangfire.Console) to display progress messages in the Hangfire dashboard. This is very useful to see the progress of a long-running job in the “Processing” view, or to see details in a job in the “Succeeded” view.
However, when running a Hangfire job that executes a (SQL Server) stored procedure that includes interim/progress “PRINT” statements, none of the output from the stored procedure was displayed in the “Processing” view, and none of the output was displayed until the Hangfire job completed.
After a bit of research, I am now able to see in-progress output from a stored procedure called from a Hangfire job. This required some changes to the stored procedure and to the Hangfire job that calls the stored procedure.
First, in the stored procedure, any PRINT statements need to be followed with “RAISERROR … WITH NOWAIT”, or displayed directly with a “RAISERROR … WITH NOWAIT” statement, such as:
PRINT @MessageVariable;
RAISERROR(N’', 0, 1) WITH NOWAIT;
Or:
RAISERROR(@MessageVariable’, 0, 1) WITH NOWAIT;
The “Severity” parameter (second parameter) for RAISERROR should typically be 0 here (a severity level less than 10 is “informational”); and the last parameter (“State”) should typically be 1 for this purpose. (You can use different numeric values in the “State” parameter to indicate progress in the procedure, but that’s less useful in this usage.)
Note: The PRINT statement buffers output and holds all messages until the entire query or loop finishes executing, but “RAISERROR… WITH NOWAIT” bypasses the buffer and flushes the message to the client immediately. To mimic a standard print statement but force it to display instantly (e.g., inside a long-running loop or step-by-step deployment script), use Severity 0 along with the WITH NOWAIT clause.
Second, the above works to display progress output when running the stored procedure directly (such as in SSMS), but when the procedure is called from .NET code (such as when using Microsoft.Data.SqlClient) using a method such as “ExecuteNonQueryAsync”, the output is not displayed until the entire (.NET code) process completes. To display in-progress messages requires 2 changes in the .NET code - using the “ExecuteReader” or “ExecuteReaderAsync” method (instead of the “ExecuteNonQueryAsync” method), and hooking up an event listener to capture and display the stored procedure PRINT output.
The following is a partial section of .NET code intended to show this process:
using (var cnx = new SqlConnection(dbConnection))
{
await cnx.OpenAsync(token).ConfigureAwait(true);
// Subscribe to the database connection's InfoMessage event and
// output any messages from the database
// (such as from PRINT statements) to the Hangfire console.
// Note that this depends on any PRINT statements also using
// "RAISERROR(N'', 0, 1) WITH NOWAIT;" or similar to flush the
// output buffer, otherwise the PRINT statements may not be sent
// until the stored procedure completes.
//
// Hook up the event listener to catch PRINT statements
cnx.InfoMessage += (sender, args) =>
{
// Write every captured SQL PRINT statement directly to the Hangfire console
taskContext!.WriteLine($"[SQL message]: {args.Message}");
};
// Note that the above also requires using ExecuteReaderAsync instead
// of ExecuteNonQueryAsync, otherwise the PRINT statements may not be sent
// until the stored procedure completes. When you use ExecuteNonQueryAsync(),
// ADO.NET waits for the entire command execution to finalize before pulling
// back the structural response packet;
// meaning it flushes all buffered InfoMessage items right at the very end
// of the method call. To bypass this network buffering and achieve true
// real-time logging on your Hangfire Dashboard, you need to
// use ExecuteReaderAsync() instead.
using (var cmd = new SqlCommand(spName, cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 3600; // 1 hour
token.ThrowIfCancellationRequested();
//await cmd.ExecuteNonQueryAsync(token).ConfigureAwait(true);
using (var reader = await cmd.ExecuteReaderAsync())
{
// Loop through all result sets to force ADO.NET to read the stream
do
{
while (await reader.ReadAsync())
{
// Even if your SP doesn't return data rows,
// walking the reader pulls the network packets.
}
} while (await reader.NextResultAsync());
}
}
}
The above (with RAISERROR in the stored procedure, and using ExecuteReaderAsync along with an event listener in the .NET code that calls the stored procedure) results in the progress messages being displayed progressively in the Hangfire console while the job is running.