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
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