SQL Server 2005中写文件的方法

--创建写文件的存储过程

ALTER proc [dbo].[p_movefile]
 @filename varchar(1000),--要操作的文本文件名  
 @text varchar(8000),   --要写入的内容
 @obj int
as  
begin
 declare @err  int,
   @src  varchar(255),
   @desc varchar(255)   
   
 exec @err=sp_oamethod @obj, 'OpenTextFile', @obj  out, @filename,8,1  
 if @err<>0  
  goto lberr  
 exec @err=sp_oamethod   @obj,'WriteLine',null,@text  
 if @err<>0  
  goto lberr  
   
 exec @err=sp_oadestroy @obj  

 return    
lberr:  
 exec sp_oageterrorinfo 0, @src out,@desc out  
 select cast(@err as  varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述
end

--调用上面的p_movefile的存储过程，将数据库查询内容写入到文件中去ALTER proc [dbo].[abc]
as
declare @SummarizationState int, @LocalUpdateID int, @TargetID int
declare   @obj   int  
declare @newline char(2)
declare @fulFileName nvarchar(256),
 @content nvarchar(4000)
 declare C Cursor for
 select SummarizationState, LocalUpdateID, TargetID from tbUpdateStatusPerComputer
begin
 --set nocount on  
 exec master..xp_cmdshell 'del  C:/sql.txt';
 --exec sp_oacreate 'Scripting.FileSystemObject',@obj out
 --exec sp_OADestroy @obj;
 exec sp_oacreate 'Scripting.FileSystemObject',@obj out
 OPEN C
 Fetch Next from C into @SummarizationState, @LocalUpdateID, @TargetID
 set @content=N'';
 set @newline='/n';
 while @@Fetch_status=0
 begin
  set @content=N'TargetID:'+convert(nvarchar(15),@TargetID);
  set @content=@content + N'    LocalUpdateID:'+ convert(nvarchar(15),@LocalUpdateID);
  set @content=@content + N'    SummarizationState:'+ convert(nvarchar(15),@SummarizationState);
  set @content=@content + '==============';

  set @fulFileName=N'C:/sql.txt'
  exec p_movefile   @fulFileName,@content,@obj

  Fetch Next from C into @SummarizationState, @LocalUpdateID, @TargetID
  
 end
 Close C
 Deallocate C 
end
go
exec abc
go