其實大家都知道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
當然我這裡是故意用RAISERROR再次拋出錯誤信息，運行結果如下：
現在我們來定義一個存儲過程，其目的就是往本地文件中寫入信息。
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'
如果遇到以下錯誤則說明Ole Automation Procedures沒有啟用
需要執行以下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語句有問題，而不能記錄具體問題。於是我想到把錯誤信心記錄報數據庫表裡面，可是當遇到問題時事務會回滾，表裡面根本就沒有錯誤信息。於是乎 只能報錯誤信息記錄到文件中了。
如：
有不對的地方還請大家拍磚哦！
