如何將sql執行的錯誤消息記錄到本地文件中實現過程_Mssql數據庫教程
      
編輯Tag賺U幣
      




      

      推薦：sql server 2008中的apply運算符使用方法
      sql server 2008中的apply運算符使用方法，需要的朋友可以參考一下
      其實大家都知道sql語句的錯誤信息都可以在sys.messages表里面找到
      

      如：
      

      

      如果在執行語句在try...catch中 我們可以通過以下方法獲取錯誤信息。sql語句如下：
      復制代碼 代碼如下:www.wf0088.com

      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腳本如下：
      復制代碼 代碼如下:www.wf0088.com
       
      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 

      ----------------------------------------
      然后執行該存儲過程：
      復制代碼 代碼如下:www.wf0088.com

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

      如果遇到以下錯誤則說明Ole Automation Procedures沒有啟用
      

      

      需要執行以下SQL：
      復制代碼 代碼如下:www.wf0088.com
       
      go 
      sp_configure 'show advanced options', 1; 
      GO 
      RECONFIGURE; 
      GO 
      sp_configure 'Ole Automation Procedures', 1; 
      GO 
      RECONFIGURE; 
      GO 

      運行即如果如圖：
      

      

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

      最后封裝一個存儲過程獲取錯誤信息，其腳本如下：
      復制代碼 代碼如下:www.wf0088.com
       
      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語句有問題，而不能記錄具體問題。于是我想到把錯誤信心記錄報數據庫表里面，可是當遇到問題時事務會回滾，表里面根本就沒有錯誤信息。于是乎 只能報錯誤信息記錄到文件中了。
      

      如：
      

      

      

      

      

      有不對的地方還請大家拍磚哦！
      分享：sql中case語句的用法淺談
      今天看資料的時候，看到了sql中使用case的一些使用方法，做個記錄
      


      

      


      來源：模板無憂//所屬分類：Mssql數據庫教程/更新時間：2013-05-20


      


      



      


      



      相關Mssql數據庫教程：