本文创建的存储过程,采用了https://www.cnblogs.com/AlbertJoey/p/4432030.html
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
GO
调用 :
exec UCreateOrAppendTextFile '文件路径' ,'文本内容'
但还需要注意一个问题,上述语句,需要启用 Ole Automation Procedures
这里,详细可看https://blog.csdn.net/luck2018/article/details/7759454
以下示例显示了如何查看 OLE Automation Procedures 的当前设置。
EXEC sp_configure 'Ole Automation Procedures';
(注,未设置前,可能执行报错)
以下示例显示了如何启用 OLE Automation Procedures。
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
(注,这四条语句,需要一条一条的执行,执行完毕后,可以执行查看语句)