sqlserver文件操作

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER    PROCEDURE USP_CREATE_TXTFILE(@FILENAME VARCHAR(200))
AS
BEGIN
 DECLARE @STRCMD VARCHAR(2048),
  @fs int,
  @ole int,
  @file int

 --删除存在的文件
 SELECT @STRCMD = 'del '+ @FILENAME
 EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
 EXEC master..xp_cmdshell @STRCMD, NO_OUTPUT
 
 --创建文件
 EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @FILENAME, 8, 1
 --写入数据
 DECLARE SysKursor INSENSITIVE SCROLL CURSOR
  FOR SELECT export_col FROM EXPORT_DOWNLOAD order by no

 OPEN SysKursor
 FETCH SysKursor INTO @STRCMD
 WHILE @@Fetch_Status = 0
 BEGIN
  EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @STRCMD
 FETCH SysKursor INTO @STRCMD
 END
 CLOSE SysKursor
 DEALLOCATE SysKursor
 --关闭文件
 EXECUTE @ole = sp_OADestroy @file
 EXECUTE @ole = sp_OADestroy @fs
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO