This is basically like this question.
The approach suggested raises one event no matter the number of prints, I'd like instead to raise one .Net event for each print in SQL code.
The idea is to send to the user notification about the progress of the long running Stored procedure.
It's not that the InfoMessage event is only fired once, but that SQL Server doesn't send PRINT output straight away. You could try using RAISERROR with the NOWAIT option instead;
RAISERROR ('My message', 0, 1) WITH NOWAIT
Using RAISERROR with a severity lower than 11 is not treated as an error condition.
In the .Net you need to set SqlConnection.FireInfoMessageEventOnUserErrors = true to receive the messages straight away. A consequence of this setting is that SQL errors will no longer raise exceptions, they will be info messages instead, so you have to check for real errors in the InfoMessage event handler.
Edit: Complete working example
I've put together a complete working example. I'm a C# guy not a VB guy, sorry about that. Make sure you read up on the FireInfoMessageEventOnUserErrors property of the connection. Here's my simple SP;
Edit 2: Enhanced working example
I've added a check for real errors in the InfoMessage event handler and added a SqlCommand.StatementCompleted event handler to show how 'X rows affected' messages fit in. I've added a lot more messages in the demo SP so it's easier to see which messages are delayed and which are received promptly.
create proc dbo.MyProc as
begin
declare @t table (i int)
set nocount off
print 'This is the 1st PRINT message.'
waitfor delay '00:00:01'
-- generate a 'rows affected' info message.
insert @t values (1)
waitfor delay '00:00:01'
print 'This is the 2nd PRINT message.'
waitfor delay '00:00:01'
insert @t values (1)
waitfor delay '00:00:01'
raiserror('This is the 1st RAISERROR NOWAIT message, severity 1.', 1, 1) with nowait
waitfor delay '00:00:01'
insert @t values (1)
waitfor delay '00:00:01'
print 'This is the 3rd PRINT message.'
waitfor delay '00:00:01'
raiserror('This is the 2nd RAISERROR NOWAIT message, severity 10.', 10, 1) with nowait
waitfor delay '00:00:01'
-- generate an error message.
raiserror('This is the 3rd RAISERROR NOWAIT message, severity 11.', 11, 1) with nowait
waitfor delay '00:00:01'
print 'This is the 4th PRINT message.'
waitfor delay '00:00:01'
-- generate a single real error.
declare @i int
select @i = 1 / 0
waitfor delay '00:00:01'
print 'This is the 5th PRINT message.'
waitfor delay '00:00:01'
insert @t values (1)
waitfor delay '00:00:01'
-- generate multiple real errors from one statement.
backup log fakedb to disk = 'xxx'
waitfor delay '00:00:01'
print 'This is the 6th PRINT message.'
waitfor delay '00:00:01'
raiserror('This is the final RAISERROR NOWAIT message, severity 10.', 10, 1) with nowait
waitfor delay '00:00:01'
print 'This is the final PRINT message.'
waitfor delay '00:00:01'
end
Here's my simple console app;
namespace RaiserrorNoWait
{
using System;
using System.Data;
using System.Data.SqlClient;
static class Program
{
static void Main()
{
using (SqlConnection connection = new SqlConnection(@"Integrated Security=SSPI;Initial Catalog=Scratch;Data Source=DESKTOP-DIR91LS\MSSQL2016"))
{
connection.InfoMessage += ConnectionInfoMessage;
connection.FireInfoMessageEventOnUserErrors = true;
connection.Open();
using (SqlCommand command = new SqlCommand("dbo.MyProc", connection))
{
command.CommandType = CommandType.StoredProcedure;
Console.WriteLine("{0:T} - Execute query...", DateTime.Now);
command.StatementCompleted += CommandStatementCompleted;
command.ExecuteNonQuery();
Console.WriteLine("{0:T} - Query finished.", DateTime.Now);
}
connection.Close();
}
Console.WriteLine("Press Enter to Exit.");
Console.ReadLine();
}
// StatementCompleted events and InfoMessage events might not fire in the expected order although
// in my very limited testing with SQL Server 2016 and .Net Framework 4.8 they appear to.
// See https://dba.stackexchange.com/questions/119334/how-can-i-get-individual-rowcounts-like-ssms
private static void CommandStatementCompleted(object sender, StatementCompletedEventArgs e)
{
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine("{0:T} - StatementCompleted: {1} rows affected", DateTime.Now, e.RecordCount);
}
private static void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// The documentation states that e.Errors will always have at least one SqlError.
// See https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlerrorcollection?view=dotnet-plat-ext-3.1
// We could assume that when FireInfoMessageEventOnUserErrors is true then the event will be raised once per error
// but the documentation does not clearly state this so we have to check each SqlError to see if it is a real error.
bool isRealError = false;
byte maxSeverity = 0;
foreach (SqlError sqlError in e.Errors)
{
isRealError = isRealError || sqlError.Class > 10;
maxSeverity = Math.Max(maxSeverity, sqlError.Class);
}
// Here we display errors in red but we could throw an exception or perform some error handling.
string messageType = isRealError ? "Error" : "Info";
Console.ForegroundColor = isRealError ? ConsoleColor.Red : ConsoleColor.White;
Console.WriteLine("{0:T} - {1}: \"{2}\" - max severity: {3}", DateTime.Now, messageType, e.Message, maxSeverity);
}
}
}
Running this I get this output, note that RAISERROR NOWAIT and real errors flush any pending info messages through.
02:38:03 - Execute query...
02:38:07 - Info: "This is the 1st PRINT message." - max severity: 0
02:38:07 - StatementCompleted: 1 rows affected
02:38:07 - Info: "This is the 2nd PRINT message." - max severity: 0
02:38:07 - StatementCompleted: 1 rows affected
02:38:07 - Info: "This is the 1st RAISERROR NOWAIT message, severity 1." - max severity: 1
02:38:10 - StatementCompleted: 1 rows affected
02:38:10 - Info: "This is the 3rd PRINT message." - max severity: 0
02:38:10 - Info: "This is the 2nd RAISERROR NOWAIT message, severity 10." - max severity: 0
02:38:11 - Error: "This is the 3rd RAISERROR NOWAIT message, severity 11." - max severity: 11
02:38:18 - Info: "This is the 4th PRINT message." - max severity: 0
02:38:18 - Error: "Divide by zero error encountered." - max severity: 16
02:38:18 - Info: "This is the 5th PRINT message." - max severity: 0
02:38:18 - StatementCompleted: 1 rows affected
02:38:18 - Error: "Database 'fakedb' does not exist. Make sure that the name is entered correctly." - max severity: 16
02:38:18 - Error: "BACKUP LOG is terminating abnormally." - max severity: 16
02:38:18 - Info: "This is the 6th PRINT message." - max severity: 0
02:38:18 - Info: "This is the final RAISERROR NOWAIT message, severity 10." - max severity: 0
02:38:20 - Info: "This is the final PRINT message." - max severity: 0
02:38:20 - Query finished.
Press Enter to Exit.
