快捷導(dǎo)航

如何將sql執(zhí)行的錯(cuò)誤消息記錄到本地文件中實(shí)現(xiàn)過(guò)程

更新時(shí)間：2013年05月19日 16:07:13 作者：

sql語(yǔ)句的錯(cuò)誤信息都可以在sys.messages表里面找到，下面與大家分享下將sql 執(zhí)行的錯(cuò)誤消息記錄到本地文件中，不會(huì)的朋友可以參考下哈

其實(shí)大家都知道sql語(yǔ)句的錯(cuò)誤信息都可以在sys.messages表里面找到

如：

如果在執(zhí)行語(yǔ)句在try...catch中我們可以通過(guò)以下方法獲取錯(cuò)誤信息。sql語(yǔ)句如下：

BEGIN TRY 
SELECT 3 / 0 
END TRY 
BEGIN CATCH 
DECLARE @errornumber INT 
DECLARE @errorseverity INT 
DECLARE @errorstate INT 
DECLARE @errormessage NVARCHAR(4000) 
SELECT @errornumber = ERROR_NUMBER() , 
@errorseverity = ERROR_SEVERITY() , 
@errorstate = ERROR_STATE() , 
@errormessage = ERROR_MESSAGE() 

SELECT @errornumber , 
@errorseverity , 
@errorstate , 
@errormessage 

RAISERROR ( 
@errormessage, -- Message text, 
@errorseverity, -- Severity, 
@errorstate, -- State, 
@errornumber 
); 
END CATCH

當(dāng)然我這里是故意用RAISERROR再次拋出錯(cuò)誤信息，運(yùn)行結(jié)果如下：

現(xiàn)在我們來(lái)定義一個(gè)存儲(chǔ)過(guò)程，其目的就是往本地文件中寫入信息。

sql腳本如下：

復(fù)制代碼代碼如下:

 
CREATE Proc [dbo].[UCreateOrAppendTextFile](@Filename VarChar(100),@Text nVarchar(4000)) 
AS 
DECLARE @FileSystem int 
DECLARE @FileHandle int 
DECLARE @RetCode int 
DECLARE @RetVal int 
DECLARE @CreateOrAppend int 

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT 
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0) 
RAISERROR ('could not create FileSystemObject',16,1) 
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'FileExists', @RetVal out, @FileName 
IF (@@ERROR|@RetCode > 0) 
RAISERROR ('could not check file existence',16,1) 
-- If file exists then append else create 
SET @CreateOrAppend = case @RetVal when 1 then 8 else 2 end 
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @Filename, @CreateOrAppend, 1 
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0) 
RAISERROR ('could not create File',16,1) 
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @text 
IF (@@ERROR|@RetCode > 0 ) 
RAISERROR ('could not write to File',16,1) 
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' 
IF (@@ERROR|@RetCode > 0) 
RAISERROR ('Could not close file ',16,1) 
EXEC sp_OADestroy @filehandle 
IF (@@ERROR|@RetCode > 0) 
RAISERROR ('Could not destroy file object',16,1) 
EXEC sp_OADestroy @FileSystem 

----------------------------------------
然后執(zhí)行該存儲(chǔ)過(guò)程：

復(fù)制代碼代碼如下:

exec UCreateOrAppendTextFile 'C:\Error.log','hello majaing'

如果遇到以下錯(cuò)誤則說(shuō)明Ole Automation Procedures沒(méi)有啟用

需要執(zhí)行以下SQL：

復(fù)制代碼代碼如下:

 
go 
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1; 
GO 
RECONFIGURE; 
GO 

運(yùn)行即如果如圖：

當(dāng)然這里運(yùn)行存儲(chǔ)過(guò)程之前必須保證文件是存在的。

最后封裝一個(gè)存儲(chǔ)過(guò)程獲取錯(cuò)誤信息，其腳本如下：

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE LOGError(@msg nvarchar(400)) 
as 
declare @text nvarchar(400) 
SELECT @text=text FROM sys.messages WHERE language_id=1033 AND message_id=@@ERROR 
if len(@text)>1 
begin 
set @msg=@msg +' : '+@text 
EXEC dbo.UCreateOrAppendTextFile 'C:\Error.log',@msg 
end 

執(zhí)行存儲(chǔ)過(guò)程及結(jié)果如下：

以上存儲(chǔ)過(guò)程在MSSQL2005、2012中測(cè)試通過(guò)。

大家都知道目前在文件系統(tǒng)中事務(wù)的實(shí)現(xiàn)還是比較復(fù)雜的，雖然在win7后我們可以用C#實(shí)現(xiàn)文件的事務(wù)，但是微軟的分布式事務(wù)Distributed Transaction Coordinator(msdtc）目前也還不支持文件事務(wù)。

這里說(shuō)說(shuō)為什么有這樣的需求吧：目前需要一個(gè)項(xiàng)目用SSIS做數(shù)據(jù)遷移，其中很大部分都是用sql語(yǔ)句實(shí)現(xiàn)的，如 insert into ....select ... from xxxx.其中原數(shù)據(jù)庫(kù)中難免有什么臟數(shù)據(jù)導(dǎo)致插入失敗，于是我在SSIS中使用msdtc服務(wù)，保證數(shù)據(jù)的一致性。雖然SSIS也有錯(cuò)誤處理，但是它只能記錄那個(gè)sql語(yǔ)句有問(wèn)題，而不能記錄具體問(wèn)題。于是我想到把錯(cuò)誤信心記錄報(bào)數(shù)據(jù)庫(kù)表里面，可是當(dāng)遇到問(wèn)題時(shí)事務(wù)會(huì)回滾，表里面根本就沒(méi)有錯(cuò)誤信息。于是乎只能報(bào)錯(cuò)誤信息記錄到文件中了。

如：