How to record SQL Execution error messages to a local file

As we all know, the error messages of SQL statements can be found in the sys. messages table.

For example:

If the execution statement is in try... catch, we can get the error information through the following methods. The SQL statement is as follows:
Copy codeThe Code is as follows:
BEGIN TRY
SELECT 1, 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

Of course, here I intentionally throw an error message with RAISERROR. The running result is as follows:

Now we define a stored procedure to write information to a local file.

The SQL script is as follows:
Copy codeThe Code is as follows:
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

----------------------------------------
Then execute the stored procedure:
Copy codeThe Code is as follows:
Exec UCreateOrAppendTextFile 'C: \ Error. log', 'Hello majaing'

If the following error occurs, Ole Automation Procedures is not enabled.

Run the following SQL statement:
Copy codeThe Code is as follows:
Go
Sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
Sp_configure 'ole Automation Procedures ', 1;
GO
RECONFIGURE;
GO

Run if

Before running the stored procedureThe file must exist..

Finally, encapsulate a stored procedure to obtain error information. The script is as follows:
Copy codeThe Code is as follows:
Create procedure LOGError (@ msg nvarchar (400 ))
As
Declare @ text nvarchar (400)
SELECT @ text = text FROM sys. messages WHERE your age_id = 1033 AND message_id = @ ERROR
If len (@ text)> 1
Begin
Set @ msg = @ msg + ':' + @ text
EXEC dbo. UCreateOrAppendTextFile 'C: \ Error. log', @ msg
End

The stored procedure and result are as follows:

The above stored procedures were tested in MSSQL2005 and 2012.

We all know that the Transaction implementation in the file system is still complicated. Although we can use C # To implement file transactions after Windows 7, Microsoft's Distributed Transaction Coordinator (msdtc) currently, file transactions are not supported.

Let's talk about why there is such a requirement: at present, a project needs to use SSIS for data migration, most of which are implemented using SQL statements, such as insert .... select... from xxxx. in the original database, it is inevitable that the insertion fails due to dirty data. Therefore, I use the msdtc Service in SSIS to ensure data consistency. Although SSIS also has error handling, it can only record the SQL statement but not the specific problem. So I thought of reporting the error confidence record to the database table. However, when a problem occurs, the current affairs will be rolled back, and there is no error message in the table. Therefore, only error messages can be reported to the file.

For example:

If something is wrong, please make a brick!