SQLServer中将文本写入文件中的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[sp_write_text]
@filename varchar(1000),--要操作的文本文件名
@text varchar(8000) --要写入的内容
as
declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int

exec @err=sp_oacreate 'Scripting.FileSystemObject',@obj out
if @err<>0 goto lberr

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 错误描述

 

--可能会报没权限--执行以下代码

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO