HoneyHoney

问题

有时候，SQL SERVER 会抛两个error message出来，比如







这个时候，如果你用ERROR_MESSAGE()，你只会拿到最后一条error message。那如何拿到所有的error message呢？

解决

Paulo Santos 创建了一个SP来实现这件事。http://pjondevelopment.50webs.com/articles/retrieveErrorMessage.htm 。我拿来以后稍稍改了一下里面的bug，现在就可以捕获所有的error message了。

IF NOT EXISTS(SELECT
                *
              FROM sys.procedures
              WHERE name = 'usp_GET_LastErrorMessage')
    EXEC ('
    CREATE PROCEDURE dbo.usp_GET_LastErrorMessage
    AS 
        DECLARE @error_message NVARCHAR(2000)
        SET @error_message = ''Stored procedure ''+OBJECT_NAME(@@PROCID)+'' not yet implemented'' 
        RAISERROR(@error_message, 16, 1)
    ')
GO

ALTER PROCEDURE dbo.usp_GET_LastErrorMessage
    @output_result BIT = 1
AS
BEGIN
  DECLARE 
    @dbccrow CHAR(77),
    @byte INT,
    @pos INT, -- position in buffer line
    @numMsg INT, -- number of error messages
    @gather INT, -- error message piece
    @count INT, -- byte position within a collection of bytes
    @messageLength INT,
    @stringLength INT,
    @nchar INT,
    @errNumber BIGINT,
    @errState INT,
    @errLevel INT,
    @errMessage NVARCHAR(1000),
    @errInstance NVARCHAR(256),
    @errProcedure NVARCHAR(256),
    @errLine INT,
    @bytesRead INT;

/*
Buffer sample
--------------------------------------------------
00000000   04 01 00 c8 00 37 01 00 aa 30 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....

Buffer description
--------------------------------------------------
0 - We need to scan the buffer for the byte marker 0xAA that starts an error message.
1 - Size of the entire error message (2 bytes)
2 - Error Number (4 bytes)
3 - Error State (1 byte)
4 - Error Level (1 byte)
5 - Size of the text, in character, of the error message (2 bytes)
6 - Variable length of bytes with the Unicode error message
7 - Size of the name of SQL Server instance (1 byte)
8 - Variable length of bytes with the Unicode name of the SQL Server instance
9 - Size of the name of the stored procedure where the error occurred (1 byte)
10 - Variable length of bytes with the Unicode name stored procedure
11 - Line where the error occurred (2 bytes)


Buffer sample as it will be parsed
--------------------------------------------------
___________________________________0#_1####_2##########_3#
00000000   04 01 00 c8 00 37 01 00 aa 3c 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
___________4#_5####_6#####################################
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
___________################################_7#_8##########
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
___________###############################################
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
___________##_9#_10_11###_________________________________
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....


1 - 0X003c = 60
2 - 0X0000C350 = 50000
3 - 0X01 = 1
4 - 0X10 = 16
5 - 0X000C = 12
6 - "simple error"
7 - 0X0B = 11
8 - "LOCALHOST"
9 - 0X0
10 - 0X0
11 - 0X0002 = 2


The problem with this approach is that if the buffer contains any user data it might have the marker byte and thus provoking a false response.
This has been mitigated by comparing the message bytes and the bytes read for the message.

*/

  -- Catch the output buffer.
  CREATE TABLE #OutputBuffer(contents CHAR(77));
  INSERT INTO #OutputBuffer EXEC('DBCC OUTPUTBUFFER(@@spid) WITH NO_INFOMSGS');

  IF OBJECT_ID('TEMPDB..#errors') IS NULL
      CREATE TABLE #errors(
        id INT,
        errNumber BIGINT,
        errState INT,
        errLevel INT,
        errMessage NVARCHAR(2000),
        errInstance NVARCHAR(256),
        errProcedure NVARCHAR(256),
        errLine INT
      );

  -- Step through the buffer lines.
  DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR SELECT contents FROM #OutputBuffer ORDER BY contents;

  -- Init variable, and open cursor.
  OPEN error_cursor;
  FETCH NEXT FROM error_cursor INTO @dbccrow;

    -- Count the number of error messages
    SET @numMsg = 0
    SET @pos    = 12
    SET @gather = 0

  -- Now assemble rest of string.
  WHILE @@FETCH_STATUS = 0
  BEGIN
Start:
    IF @pos > 57
    BEGIN
      SET @pos = 12;
      GOTO NextRow;
    END;

    -- Get a byte from the stream
    SET @byte = CAST(CONVERT(VARBINARY(2), SUBSTRING(@dbccrow, @pos, 2), 2) AS INT)

    -- move to the next byte
    SET @pos = @pos + 3

    -- Searching for the 0xAA marker
    IF @gather = 0
    BEGIN
      IF @byte != 170 GOTO Start;
      SELECT @gather = 1, @bytesRead = 0, @count = 0, @messageLength = 0;
      GOTO Start;
    END;

    SET @bytesRead += 1; -- record number of bytes read for message
--    IF @bytesRead > @messageLength AND @gather > 1 GOTO NextRow; -- bail out if we have read beyond the message length

    -- Get the message length
    IF @gather = 1
    BEGIN
      IF @count = 0 SET @messageLength = 0;
      SET @messageLength += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @count = 0, @gather = 2;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 2
    BEGIN
      IF @count = 0 SET @errNumber = 0;
      SET @errNumber += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 4 SELECT @count = 0, @gather = 3;
      GOTO Start
    END;

    -- Get the Error State
    IF @gather = 3
    BEGIN
      SELECT @gather = 4, @errState = @byte;
      GOTO Start
    END;

    -- Get the Error Level
    IF @gather = 4
    BEGIN
      SELECT @gather = 5, @errLevel = @byte;
      GOTO Start;
    END;

    -- Get the error message length
    IF @gather = 5
    BEGIN
      SET @stringLength = ISNULL(@stringLength, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @nchar = 0, @count = 0, @gather = 6;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 6
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errMessage = ISNULL(@errMessage, '') + nchar(@nchar);
        IF LEN(@errMessage) = @stringLength SET @gather = 7;
        GOTO Start;
      END;
      ELSE SET @gather = 7;
    END;

    -- Get the instance name size
    IF @gather = 7
    BEGIN
      SELECT @gather = 8, @stringLength = @byte;
      GOTO Start;
    END;

    -- Get the instance name
    IF @gather = 8
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errInstance = ISNULL(@errInstance, '') + nchar(@nchar);
        IF LEN(@errInstance) = @stringLength SET @gather = 9;
        GOTO Start;
      END;
      ELSE SET @gather = 9;
    END;

    -- Get the procedure name size
    IF @gather = 9
    BEGIN
      SELECT @gather  = 10, @stringLength = @byte, @nchar = 0;
      GOTO Start;
    END;

    -- Get the procedure name
    IF @gather = 10
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1
        IF @count = 2 SELECT @count = 0, @errProcedure = ISNULL(@errProcedure, '') + nchar(@nchar);
        IF LEN(@errProcedure) = @stringLength SET @gather = 11;
        GOTO Start
      END;
      ELSE SET @gather = 11;
    END;

    -- Get the error line
    IF @gather = 11
    BEGIN
      SET @errLine = ISNULL(@errLine, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;

      IF @count = 2
      BEGIN
        SET @numMsg += 1;
        -- validate the message and insert
        IF @bytesRead = @messageLength INSERT #errors VALUES(@numMsg, @errNumber, @errState, @errLevel, @errMessage, @errInstance, @errProcedure, @errLine);
        -- reset variables
        SELECT @errMessage = NULL, @errInstance = NULL, @errProcedure = NULL, @errLine = NULL;
        SET @gather = 0
        SET @count = 0
        SET @nchar = 0;
      END;

      GOTO Start;
    END;

NextRow:
    FETCH NEXT FROM error_cursor INTO @dbccrow;
  END;

  CLOSE error_cursor;
  DEALLOCATE error_cursor;
  IF @output_result = 1
    SELECT * FROM #errors ORDER BY id;
END;
GO

IF OBJECT_ID('dbo.usp_GET_LastErrorMessage') IS NOT NULL
    PRINT '*** CREATED PROCEDURE dbo.usp_GET_LastErrorMessage ***'
ELSE
    PRINT '*** FAILED CREATING PROCEDURE dbo.usp_GET_LastErrorMessage ***'
GO

部署上面的SP以后，

IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)

GO
EXEC usp_GET_LastErrorMessage;

你就可以看到所有的error message了。




看上去挺不错了吧。

延伸

但是，如果你想通过TRY/CATCH来根据里面的error number进行不同的异常处理，那你还是洗洗睡吧！最主要的问题在于：直到你THROW了整个error，上面的SP才会捕获到异常信息。但是一旦已经抛异常了，TSQL就不会再继续往下执行了，你也就更加没法进行异常捕获操作了。

IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

BEGIN TRY
ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)
END TRY
BEGIN CATCH
    EXEC usp_GET_LastErrorMessage; -- Empty
    THROW;
END CATCH
GO

EXEC usp_GET_LastErrorMessage; -- Show Data

上面就是一个例子，第一次执行SP的时候，你会得到一个空结果集，第二次执行SP的时候，你就可以拿到正常的结果集了。基于这点，我的任务就没法完成了，真是然并卵！