How will the implementation of the SQL error log messages to a local file
In fact, we all know that SQL statement error information can be found in the sys.messages table.
Such as:
We can get the error information through the following methods if the statement in the try... Catch. The SQL statement is as follows:

Of course, I'm here to RAISERROR once again thrown error information, operation results are as follows:
Now we come to the definition of a stored procedure, its purpose is to a local file write information.
SQL script:
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 executes the stored procedure:
exec UCreateOrAppendTextFile 'C:\Error.log','hello majaing'
If encountered the following error indicates that Ole Automation Procedures is not enabled
Need to execute the following SQL:
go sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
If the operation is shown in Figure:
Of course, here before running the stored procedure must ensure that the file exists.
Finally, encapsulation of a stored procedure to obtain the error message, the script:
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
The stored procedure is executed and the results are as follows:
These stored procedures in MSSQL2005 test, 2012.
We all know that the current implementation of the transaction in the file system is more complex, although after win7 we can realize the file with the C# transaction, but Microsoft distributed transaction Distributed Transaction Coordinator (MSDTC) is currently does not support file affairs.
Here to talk about why there is such demand: the need to do a project of data transfer with SSIS, most of which are implemented with the SQL statement, such as insert into....select... From xxxx. the primary database is any dirty data to insert failed, so I use the MSDTC service in SSIS, consistent with the data. Although some SSIS error handling, but it can only record the SQL statement has a problem, but not recorded the specific problem. So I think the error confidence record database tables, but when we meet problems transactions are rolled back, there's no error information table. So the only error information to the file.
Such as:
Have a wrong place also please Paizhuan oh!
Posted by Christ at November 20, 2013 - 7:09 PM