首页 > 代码库 > SQL SERVER调用textcopy写文件

SQL SERVER调用textcopy写文件

2024-08-18 03:31:40   220人阅读

SET @PATH = ‘textcopy /S ‘ + @LServer +

        ‘ /U ‘+ @LUser +

        ‘ /P ‘+ @LPass +

        ‘ /D ‘+ @LDB +

        ‘ /T ‘+@table+

        ‘ /C ‘ +@column+

        ‘ /W ‘+@Lwhere

        ‘ /F ‘ + @LPath +‘\‘+  @File + ‘ /O‘

EXEC master..xp_cmdshell @Path

/**说明

@LServer:服务器名

@LUser:用户名 

@LPass:密码

@LDB:数据库

@table:表名

@column:列名

@Lwhere:查询条件

@LPath:路径

@File: 文件名

***/

 

 一个成功的实例 :

declare @where varchar(200)
declare @file varchar(500)
declare @PATH varchar(800)
declare @id int,@stockcode  varchar(20),@stocksname varchar(20),@declaredate datetime,@F001 varchar(20)

declare sp cursor for
select id,stockcode,stocksname,declaredate,f001
from  t1
open sp
fetch next from sp into @id,@stockcode,@stocksname,@declaredate,@F001
while(@@fetch_status=0)
begin
set @where=‘"where id=‘+convert(varchar(20),@id)+‘"‘
set @file=‘(‘+@stockcode+‘)‘+@stocksname+‘(‘+convert(char(8),@declaredate,112)+‘)‘+@F001+‘.txt‘
SET @PATH = ‘textcopy /S yubaolong\s2‘ + 
        ‘ /U ‘+ ‘sa‘ +
        ‘ /P ‘+ ‘123‘ +
        ‘ /D ‘+ ‘db40‘+
        ‘ /T ‘+‘t1‘+
        ‘ /C ‘ +‘F002‘+
        ‘ /W ‘+@where+‘ /F ‘ + ‘d:\111‘ +‘\‘+@file+ ‘ /O‘
--        ‘" /F ‘ + ‘F:\PDF\11.PDF‘ + ‘ /O‘ 
EXEC master..xp_cmdshell @Path

 fetch next from sp into @id,@stockcode,@stocksname,@declaredate,@F001
end
close sp
deallocate sp

 

-------------------------------------------------------------------

SQL SERVER调用textcopy写文件


联系
我们