USE [DB_CS] --10.X.X.X
GO
/****** Object: StoredProcedure [dbo].[SP_FTP] Script Date: 2017/2/18 下午 02:36:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[SP_FTP]
as
Declare @yyyymmdd varchar(8)
select @yyyymmdd = (select MAX(datadate) from dateforreport)
Declare @strsql varchar(8000)
Declare @strnul varchar(8000)
Declare @filename varchar(1000)
Declare @Name varchar(100)
SET @Name ='test'
--串檔案名稱
select @filename = 'TEST_' + @yyyymmdd + '.txt'
select @strnul = 'TYPE nul > D:\TEST\' + @filename
--先將檔案create好
exec master..xp_cmdshell @strnul
--串下載語法
select @strsql = 'bcp "SELECT '''+@Name+''',datadate,substring(unino,1,7)+''***'' As unino,txno,tlrno FROM [LINKSERVER].DBNAME.dbo.TABLENAME where datadate = ' + @yyyymmdd + '" queryout D:\TEST\' + @filename + ' -c -T -S 10.X.X.X -U 帳號 -P 密碼'
--下傳暫存資料表內容到檔案
exec master..xp_cmdshell @strsql
--寫put_script檔案
Declare @cmd varchar(8000)
Declare @FTPUser varchar(128),
@FTPPWD varchar(128),
@usingfile varchar(128),
@exeFile varchar(128)
/* 設定參數 */
select @exeFile = 'D:\TEST\put_script.bat'
select @usingfile = 'D:\TEST\' + @filename
select @FTPUser = 'test'
select @FTPPWD = 'test'
--編輯put_script檔案內容
select @cmd = 'echo ' + 'open FTPID_ADDR' + ' > ' + @exeFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @exeFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD + '>> ' + @exeFile
exec master..xp_cmdshell @cmd
--select @cmd = 'echo bin >> ' + @exeFile
--exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @usingfile + ' ' + @filename + ' >> ' + @exeFile
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @exeFile
exec master..xp_cmdshell @cmd
--執行檔案
select @cmd = 'ftp -s:' + @exeFile
exec master..xp_cmdshell @cmd
