主页 / dba / 问题 / 328685
Accepted
Vojtěch Dohnal
Asked: 2023-06-27 19:03:18 +0800 CST

如何刷新 SSMS 中的消息窗格

  • 772

我需要更频繁地刷新 SSMS 中的“消息”窗格，有没有办法在没有 GO 语句的情况下实现这一目标？

我的查询是：

 DECLARE @Deleted_Rows INT @Deleted_Rows = 1;
 DECLARE @Deleted_Rows_Total bigINT = 0;
 DECLARE @t1 DATETIME;
 DECLARE @t2 DATETIME;

 WHILE (@Deleted_Rows > 0)
   BEGIN
      SET @t1 = GETDATE();
      DELETE TOP (100000)  FROM  PenData WHERE Cas < '20200101'
      SET @Deleted_Rows = @@ROWCOUNT;
      SET @t2 = GETDATE();

      SET @Deleted_Rows_Total = @Deleted_Rows_Total +@Deleted_Rows
      PRINT DATEDIFF(SECOND,@t1,@t2)
      PRINT @Deleted_Rows_Total

      --SELECT @Deleted_Rows AS smazano, @Deleted_Rows_Total AS celkem, DATEDIFF(SECOND,@t1,@t2) AS elapsed_s, GETDATE() as CAS
 END
sql-server

2 个回答

  1. Best Answer

    RAISERROR ... WITH NOWAIT可以用来代替（或补充）PRINT使 SQL Server 立即将挂起的结果发送到客户端。

    下面的示例使用RAISERROR ... WITH NOWAIT空字符串和零严重性来立即返回前面语句的输出PRINT。

    WHILE (@Deleted_Rows > 0)
   BEGIN
      SET @t1 = GETDATE();
      DELETE TOP (100000)  FROM  PenData WHERE Cas < '20200101'
      SET @Deleted_Rows = @@ROWCOUNT;
      SET @t2 = GETDATE();

      SET @Deleted_Rows_Total = @Deleted_Rows_Total +@Deleted_Rows
      PRINT DATEDIFF(SECOND,@t1,@t2)
      PRINT @Deleted_Rows_Total

      RAISERROR('', 0, 0) WITH NOWAIT; --flush message buffer immediately

      UPDATE dbo.MyLog
      SET CurrentStatus = CONCAT('Seconds:',DATEDIFF(SECOND,@t1,@t2), 'Deleted rows:', @Deleted_Rows_Total);
      WHERE Id = @logId

END
    • 4

  2. 不完全是答案，但我看到人们通过更新专用表中的行来解决 SSMS 的这一限制，其中长时间运行的程序报告其当前状态。

    例如类似的东西

    CREATE TABLE dbo.MyLog(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    CurrentStatus VARCHAR(4000)
);


DECLARE @Deleted_Rows INT @Deleted_Rows = 1;
DECLARE @Deleted_Rows_Total bigINT = 0;
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

INSERT INTO dbo.MyLog(CurrentStatus)
VALUES ('Start');

DECLARE @logId INT = SCOPE_IDENTITY();

WHILE (@Deleted_Rows > 0)
   BEGIN
      SET @t1 = GETDATE();
      DELETE TOP (100000)  FROM  PenData WHERE Cas < '20200101'
      SET @Deleted_Rows = @@ROWCOUNT;
      SET @t2 = GETDATE();

      SET @Deleted_Rows_Total = @Deleted_Rows_Total +@Deleted_Rows
      PRINT DATEDIFF(SECOND,@t1,@t2)
      PRINT @Deleted_Rows_Total

      UPDATE dbo.MyLog
      SET CurrentStatus = CONCAT('Seconds:',DATEDIFF(SECOND,@t1,@t2), 'Deleted rows:', @Deleted_Rows_Total);
      WHERE Id = @logId

END
    • 1

相关问题