sql写入文件

首先配置数据库属性 sp_configure 'show advanced options', 1 go reconfigure go sp_configure 'xp_cmdshell', 1 go reconfigure go sp_configure 'ole automation procedures', 1; go reconfigure; go 创建检查文件是否存在的存储过程 CREATE proc [dbo].[MonitorCheckFileExists] @fileName nvarchar(max), @fileFullName nvarchar(max), @checkFileExists int output, @title nvarchar(max) output as if (@fileName='installed-module') set @title ='GUID,Original DisplayName,New DisplayName,Original ExpirationDate,New ExpirationDate,Original Installed,New Installed,Original Module Enabled,New Module Enabled,Insert Update Or Delete,Update Time' declare @runSQL nvarchar(max) set @runSQL='master..xp_fileexist '+ ''''+@fileFullName+'''' declare @temp_xp_fileexist table (a bit,b bit,c bit) insert into @temp_xp_fileexist exec sp_executesql @runSQL if exists(select 1 from @temp_xp_fileexist where a=0) set @checkFileExists=0 else set @checkFileExists=1 return GO 创建写入文件的存储过程 CREATE proc [dbo].[MonitorReordToFile] @fileName nvarchar(max), @message nvarchar(max), @mode nvarchar(50) as declare @err int,@src varchar(255),@desc varchar(255) declare @obj int declare @fileFullName nvarchar(max) declare @checkFileExists int declare @title nvarchar(max) set @fileFullName='C:\var\opt\salsa\data\pos\audit\'+@fileName+'.csv' exec MonitorCheckFileExists @fileName,@fileFullName,@checkFileExists output,@title output exec @err=sp_oacreate 'Scripting.FileSystemObject',@obj out if @err<>0 goto lberr exec @err=sp_oamethod @obj,'OpenTextFile',@obj out,@fileFullName,8,1 if @err<>0 goto lberr if @checkFileExists=0 begin exec @err=sp_oamethod @obj,'WriteLine',null,@title if @err<>0 goto lberr end exec @err=sp_oamethod @obj,'WriteLine',null,@message if @err<>0 goto lberr exec @err=sp_oadestroy @obj return lberr: INSERT INTO [Error_log].[dbo].[tblERRORS] ([Error_Number] ,[Error_Description] ,[Error_TimeStamp] ,[UserID] ,[Subroutine] ,[Station] ,[Error_severity]) VALUES ('WriteFileError' ,''''+@message + ''' can''t write to file '''+@fileFullName+'''' ,GETDATE() ,'' ,'MonitorReordToFile' ,@mode ,'ERROR') GO 触发器-当数据修改时写入文件 CREATE TRIGGER [dbo].[RecordLogForInstalledModules] ON [dbo].[tblINSTALLED_MODULES] AFTER insert, UPDATE,delete AS BEGIN declare @fileName nvarchar(max) declare @message nvarchar(max) declare @mode nvarchar(50) declare @OriginalDisplayName nvarchar(50) declare @DisplayName nvarchar(50) declare @OriginalExpiration_Date nvarchar(20) declare @Expiration_Date nvarchar(20) declare @OriginalInstalled char(1) declare @Installed char(1) declare @OriginalModule_Enabled char(1) declare @Module_Enabled char(1) -- insert if exists(select * FROM inserted) and not exists(SELECT * from deleted) begin select @DisplayName=DisplayName,@Expiration_Date=CONVERT(varchar,Expiration_Date, 120 ), @Installed=Installed,@Module_Enabled=Module_Enabled from inserted set @mode='insert' end -- update else if exists(select * FROM inserted) and exists(SELECT * from deleted) begin select @DisplayName=i.DisplayName,@Expiration_Date=CONVERT(varchar,i.Expiration_Date, 120 ),@Installed=i.Installed,@Module_Enabled=i.Module_Enabled, @OriginalDisplayName=d.DisplayName,@OriginalExpiration_Date=CONVERT(varchar,d.Expiration_Date, 120 ),@OriginalInstalled=d.Installed,@OriginalModule_Enabled=d.Module_Enabled from inserted as i inner join deleted as d on i.HS_Module=d.HS_Module set @mode='update' end -- delete else if not exists(select * FROM inserted) and exists(SELECT * from deleted) begin select @OriginalDisplayName=DisplayName,@OriginalExpiration_Date=CONVERT(varchar,Expiration_Date, 120 ),@OriginalInstalled=Installed,@OriginalModule_Enabled=Module_Enabled from deleted set @mode='delete' end if isnull(@OriginalDisplayName,'')='' set @OriginalDisplayName=' ' if isnull(@DisplayName,'')='' set @DisplayName=' ' if CHARINDEX(',',@OriginalDisplayName)>0 set @OriginalDisplayName='"'+@OriginalDisplayName+'"' if CHARINDEX(',',@DisplayName)>0 set @DisplayName='"'+@DisplayName+'"' if isnull(@OriginalExpiration_Date,'')='' set @OriginalExpiration_Date=' ' if isnull(@Expiration_Date,'')='' set @Expiration_Date=' ' if isnull(@OriginalInstalled,'')='' set @OriginalInstalled=' ' if isnull(@Installed,'')='' set @Installed=' ' if isnull(@OriginalModule_Enabled,'')='' set @OriginalModule_Enabled=' ' if isnull(@Module_Enabled,'')='' set @Module_Enabled=' ' if (@OriginalDisplayName<>@DisplayName or @OriginalExpiration_Date<>@Expiration_Date or @OriginalInstalled<>@Installed or @OriginalModule_Enabled<>@Module_Enabled) begin set @message= cast(NewID() as varchar(255))+','+@OriginalDisplayName+','+@DisplayName +','+@OriginalExpiration_Date+','+@Expiration_Date+','+@OriginalInstalled+',' +@Installed+','+@OriginalModule_Enabled+','+@Module_Enabled +','+@mode+','+CONVERT(varchar,GETDATE(), 120 ) set @fileName='installed-module' exec MonitorReordToFile @fileName,@message,@mode end END GO

转载于:https://www.cnblogs.com/xuyuchen/p/8283031.html