SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.199 Pro] - Lines is Messages pane in wrong order

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.199 Pro] - Lines is Messages pane in wrong order
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post [SA 9.0.199 Pro] - Lines is Messages pane in wrong order Reply with quote
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.
Tue Jan 24, 2017 10:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
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?
Wed Jan 25, 2017 10:10 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
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).
Thu Jan 26, 2017 4:09 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.