其實大家都知道sql語句的錯誤信息都可以在sys.messages表里面找到

如：

如果在執行語句在try...catch中 我們可以通過以下方法獲取錯誤信息。sql語句如下：

復制代碼 代碼如下:

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

現在我們來定義一個存儲過程，其目的就是往本地文件中寫入信息。

sql腳本如下：

復制代碼 代碼如下:

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

復制代碼 代碼如下:

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

需要執行以下SQL：

復制代碼 代碼如下:

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

當然這里運行存儲過程之前必須保證 文件是存在的。

最后封裝一個存儲過程獲取錯誤信息，其腳本如下：

復制代碼 代碼如下:

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

以上存儲過程在MSSQL2005、2012中測試通過。

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

這里說說為什么有這樣的需求吧：目前需要一個項目用SSIS做數據遷移，其中很大部分都是用sql語句實現的， 如 insert into ....select ... from xxxx.其中原數據庫中難免有什么臟數據導致插入失敗，于是我在SSIS中使用msdtc服務，保證數據的一致性。雖然SSIS也有錯誤處理，但是它只能記錄那個sql語句有問題，而不能記錄具體問題。于是我想到把錯誤信心記錄報數據庫表里面，可是當遇到問題時事務會回滾，表里面根本就沒有錯誤信息。于是乎 只能報錯誤信息記錄到文件中了。

如：

有不對的地方還請大家拍磚哦！