[SA 9.0.199 Pro] - Lines is Messages pane in wrong order

[SA 9.0.199 Pro] - Lines is Messages pane in wrong order

There's a small piece of code:

Code:

BEGIN TRANSACTION

DECLARE @src_count int, @dest_count int;
SELECT @src_count = COUNT(1) FROM DATAHOUSE.dbo.Adatblokk AS s WHERE 1 = 1 AND '20110101' <= s.DT AND s.DT < '20110103';
SELECT @dest_count = COUNT(1) FROM DATAHOUSE_2011_Q1.dbo.Adatblokk as d WHERE 1 = 1 AND '20110101' <= d.DT AND d.DT < '20110103';
SELECT @src_count AS src_count, @dest_count AS dest_count, min(d.DT) as maxi_2011_Q1 FROM DATAHOUSE_2011_Q1.dbo.Adatblokk as d WHERE 1 = 1 AND '20110101' <= d.DT AND d.DT < '20110103';

DECLARE @message nvarchar(2047);
IF @src_count != @dest_count
BEGIN
SET @message = 'Count mismatch in DATAHOUSE_2011_Q1, src(%16li) vs dest(%16li)';
RAISERROR(@message, 1, 1, @src_count, @dest_count) WITH NOWAIT;
SET NOEXEC ON;
END
ELSE
BEGIN
DECLARE @roco int, @delete_batch_size int;
SET @delete_batch_size = 100;
WHILE @src_count > 0
BEGIN
DELETE TOP(@delete_batch_size) d FROM DATAHOUSE.dbo.Adatblokk AS d WHERE 1 = 1 AND '20110101'<= d.DT AND d.DT < '20110103';
SET @roco = @@ROWCOUNT;
SET @src_count = @src_count - @roco;
SET @message = 'Processed: (%16li), still left: (%16li)';
RAISERROR (@message, 1, 1, @roco, @src_count) WITH NOWAIT;
CHECKPOINT ; -- marks log space reusable in simple recovery
END
END;
GO

ROLLBACK TRANSACTION

and after running it in SQL Editor there's a rather unexpected reply in the Messages pane:

Quote:

Executing selected queries... Please wait or close this tab to cancel execution.
1 row fetched (522.43 ms).
100 rows affected.
100 rows affected.
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 778)
100 rows affected.
100 rows affected.
100 rows affected.
100 rows affected.
100 rows affected.
100 rows affected.
78 rows affected.
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 678)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 578)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 478)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 378)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 278)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 178)
Msg 50000, Level 1, State 1, Processed: ( 100), still left: ( 78)
Msg 50000, Level 1, State 1, Processed: ( 78), still left: ( 0)
The command(s) completed successfully (3.01 s).

I've also noticed that the messages are only returned when the execution stops at the end and not when the RAISERROR ... WITH NOWAIT statements are executed.

Executing the same code in SSMS produces the following messages (in proper order and with proper timing):

Quote:

(1 row(s) affected)

(100 row(s) affected)
Processed: ( 100), still left: ( 778)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 678)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 578)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 478)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 378)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 278)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 178)
Msg 50000, Level 1, State 1

(100 row(s) affected)
Processed: ( 100), still left: ( 78)
Msg 50000, Level 1, State 1

(78 row(s) affected)
Processed: ( 78), still left: ( 0)
Msg 50000, Level 1, State 1

In addition I've noticed a few times that when a query fails, the error message is not visible in the Messages pane after the execution stops but it is shown immediately (before even failing again) when I hit F5 again.

UPDATE: Using ODBC connection it behaves as in SSMS (but ODBC has its own glitches so I'd prefer ADO.NET).

UPDATE2: Another issue: even running the above script in ODBC connection in SE it stops after some ~3-6 minutes when deleting large number of records (~8-10M) as if it has met the criterion of @src_count > 0 even though it has not. Running it from SSMS it still crawls after ~11 minutes and I guess it'll go all the way to the end.

May I ask you to try the same after removing statement ending semicolons? Do you get the same result?

One more test, if you slightly change the original code as
BEGIN TRANSACTION
...
ROLLBACK TRANSACTION
GO

Is the result consistent with the original?

Removing the semicolons and adding GO after ROLLBACK, thus having the following code

Code:

BEGIN TRANSACTION

DECLARE @src_count int, @dest_count int
SELECT @src_count = COUNT(1) FROM DATAHOUSE.dbo.Adatblokk AS s WHERE 1 = 1 AND '20110701' <= s.DT AND s.DT < '20110702'
SELECT @dest_count = COUNT(1) FROM DATAHOUSE_2011_Q3.dbo.Adatblokk as d WHERE 1 = 1 AND '20110701' <= d.DT AND d.DT < '20110702'
SELECT @src_count AS src_count, @dest_count AS dest_count, min(d.DT) as maxi_2011_Q3 FROM DATAHOUSE_2011_Q3.dbo.Adatblokk as d WHERE 1 = 1 AND '20110701' <= d.DT AND d.DT < '20110702'

DECLARE @message nvarchar(2047)
IF @src_count != @dest_count
BEGIN
SET @message = 'Count mismatch in DATAHOUSE_2011_Q1, src(%16li) vs dest(%16li)'
RAISERROR(@message, 1, 1, @src_count, @dest_count) WITH NOWAIT
SET NOEXEC ON
END
ELSE
BEGIN
DECLARE @roco int, @delete_batch_size int
SET @delete_batch_size = 100
WHILE @src_count > 0
BEGIN
DELETE TOP(@delete_batch_size) d FROM DATAHOUSE.dbo.Adatblokk AS d WHERE 1 = 1 AND '20110701'<= d.DT AND d.DT < '20110702'
SET @roco = @@ROWCOUNT
SET @src_count = @src_count - @roco
SET @message = 'Processed: (%16li), still left: (%16li)'
RAISERROR (@message, 1, 1, @roco, @src_count) WITH NOWAIT
CHECKPOINT -- marks log space reusable in simple recovery
END
END
GO

ROLLBACK TRANSACTION
GO

caused no changes on ADO.NET connection. The slight deviation in the code (different dates and archive database name come from the original test dataset no longer being available).
Running it on ODBC connection made no difference either. The iteration still stopped after the 50th pass (perhaps that number is meaningful).