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:

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

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