如何将sql执行的错误消息记录到本地文件中实现过程

其实大家都知道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语句有问题，而不能记录具体问题。于是我想到把错误信心记录报数据库表里面，可是当遇到问题时事务会回滚，表里面根本就没有错误信息。于是乎 只能报错误信息记录到文件中了。

如：

有不对的地方还请大家拍砖哦！