SQL Server 2000 获取错误信息（不是在catch块中，以及在2000以下数据库）

• 在sqlserver 2000数据库中，没有像sqlserver 2005一样的try...catch语句，然后可以在catch语句中获取错误信息，针对这种情况需要开发一个过程来获取错误信息，具体如下：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--Mudassar Khan
/*
PRINT 1/0
DECLARE @err_num INT
SELECT @err_num=@@ERROR
DECLARE @errMsg VARCHAR(1000)
EXEC sp_GetErrorDesc_2000 @ErrorNo=@err_num, @ErrorDesc=@errMsg OUTPUT
SELECT @err_num, @errMsg
*/

ALTER PROCEDURE sp_GetErrorDesc_2000
	@ErrorNo int, 
	@ErrorDesc varchar(1000) output
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #Log
	(
		Data varchar(8000),
		ContinuationRow int
	)


	DECLARE @sql varchar (100)
	SELECT @sql = '.master.dbo.xp_readerrorlog'
	INSERT #Log
	EXEC @sql


	DECLARE @err_no varchar(1000), 
			@err_mesg varchar(1000),
			@date varchar (30), 
			@charidx int 

	SELECT TOP 1 @err_no=data FROM #Log
	WHERE data LIKE '%' + CONVERT(varchar(4), @ErrorNo) + '%' ORDER BY data DESC

	--SELECT * FROM #Log WHERE
	-- data like '%' + @data + '%'

	SET  @charidx = CHARINDEX('spid', @err_no)
	SET @date = SUBSTRING(@err_no,0,@charidx)

	SELECT  @err_mesg = data FROM #Log 
	WHERE data LIKE '%' + @date + '%' 
	AND data NOT LIKE '%Error%'
	DROP table #Log

	SET @err_no = LTRIM(SUBSTRING(@err_no, @charidx + 7, LEN(@err_no)))
	SET @err_mesg = LTRIM(SUBSTRING(@err_mesg, @charidx + 7, LEN(@err_mesg)))
	SET @ErrorDesc = @err_no + char(13) + 'Desc: ' + @err_mesg + char(13) + 'Date: ' + @date 

	if @ErrorDesc is NULL
	BEGIN
		SET @ErrorDesc = 'No Log Error Message(Error_Numer='+CAST(@ErrorNo AS VARCHAR(30))+')'
		exec sp_altermessage @ErrorNo, 'WITH_LOG', 'TRUE'
	END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

注意：因为上面代码是从日志中获取错误信息，而在错误第一次发生时，因为没有注册要登记指定错误号到日志，所以查不到对应日志，这时会返回一个“No Log Error...”的错误。只有再次发生这个错误时，才会返回真正的错误信息。

• 在Sql 2005以上的数据库获取错误的过程如下：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

/*
PRINT 1/0
DECLARE @err_num INT
SELECT @err_num=@@ERROR
DECLARE @errMsg VARCHAR(1000)
EXEC sp_GetErrorDesc_2005 @ErrorNo=@err_num, @ErrorDesc=@errMsg OUTPUT
SELECT @err_num, @errMsg
*/

ALTER PROCEDURE sp_GetErrorDesc_2005
	@ErrorNo int, 
	@ErrorDesc varchar(1000) output
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #Log08
	(
		LogDate DATETIME,
		ProcessInfo VARCHAR(300),
		[Text] VARCHAR(7000)	
	)

	--
	DECLARE @sql varchar (100)
	SELECT @sql = '.master.dbo.xp_readerrorlog'
	INSERT #Log08
	EXEC @sql
	
	--
	SELECT TOP 1 @ErrorDesc=aa.Data
	FROM (
		SELECT a.LogDate, ISNULL(CONVERT(VARCHAR(30),a.LogDate,120),'')+','+  ISNULL(a.[Text],'')+ISNULL(b.[Text],'') AS 'Data'
		FROM (
			SELECT *
			FROM #Log08 AS tt
			WHERE (
					tt.[Text] LIKE '%错误%'
					OR tt.[Text] LIKE '%Error%'
				)	
				AND tt.[Text] LIKE '%'+CAST(@ErrorNo AS VARCHAR(20))+'%'
		) AS a
		JOIN (
			SELECT *
			FROM #Log08 AS tt2	
		) AS b
		ON b.LogDate = a.LogDate 
			AND b.ProcessInfo = a.ProcessInfo 
			AND b.[Text] <> a.[Text]
	) AS aa
	ORDER BY aa.LogDate DESC	
	
	--
	if @ErrorDesc is NULL
	BEGIN
		SET @ErrorDesc = 'No Log Error Message(Error_Numer='+CAST(@ErrorNo AS VARCHAR(30))+')'
		exec sp_altermessage @ErrorNo, 'WITH_LOG', 'TRUE'
	END
	
	--
	DROP TABLE #Log08
END

GO

• 最终写一个总的获取错误信息的过程如下，即：可以自动检查当前数据是2000，还是2005来返回错误信息，代码如下：

/****** Object:  StoredProcedure [dbo].[sp_GetErrorDesc]    Script Date: 09/20/2021 09:28:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
PRINT 1/0
DECLARE @err_num INT
SELECT @err_num=@@ERROR
DECLARE @errMsg VARCHAR(1000)
EXEC sp_GetErrorDesc @ErrorNo=@err_num, @ErrorDesc=@errMsg OUTPUT
SELECT @err_num, @errMsg
*/

ALTER PROCEDURE [dbo].[sp_GetErrorDesc]
	@ErrorNo int, 
	@ErrorDesc varchar(1000) output
AS
BEGIN
	SET NOCOUNT ON;

	--
	DECLARE @CurProductMajorVer INT
	SELECT @CurProductMajorVer=CAST(ugsi.ProductMajorVersion AS INT)
	FROM dbo.UDF_GetServerInfo() AS ugsi
	
	--
	IF @CurProductMajorVer<9  --SQL Server 2000 and below
	BEGIN
		EXEC sp_GetErrorDesc_2000
			@ErrorNo = @ErrorNo,
			@ErrorDesc = @ErrorDesc OUTPUT
	END
	ELSE	--SQL Server 2005 and above
	BEGIN
		EXEC sp_GetErrorDesc_2005
			@ErrorNo = @ErrorNo,
			@ErrorDesc = @ErrorDesc OUTPUT
	END	
END


GO

注意：上面代码中的UDF_GetServerInfo函数可以参照另一个文章获取。