December 11, 2008 at 7:03 am
I have following sp which i have created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO
Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as
declare @cmd varchar(1000)
declare @workfilename varchar(128)
DECLARE @returncode int
/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/
select @workfilename = 'ftpcmd.txt'
-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),' ')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),' ')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),' ')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),' ')
select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @workdir + @workfilename
DECLARE @result TABLE (id int identity(1,1), s varchar(1000))
insert @result
exec @returncode = master..xp_cmdshell @cmd
IF(@returncode = 0)
INSERT @result SELECT 'FTP command successfully executed'
select [output] = s from @result WHERE s IS NOT NULL
AND s NOT LIKE 'quit'
go
--------
when i run SP
exec s_ftp_putfile
@FTPServer = 'ftp://san03:1208 ' ,
@FTPUser = 'testftp' ,
@FTPPWD = 'testpwd' ,
@FTPPath = '/Volume_2/SVR03/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'D:\xml1\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\'
i got error
Unknown host ftp://san03:1208 .
Invalid command.
Invalid command.
Not connected.
open ftp://san03:1208
testftp
testpwd
put D:\xml1\test.txt /Volume_2/SVR03/test.txt
FTP command successfully executed
Can have any idea ? or can give me inforamation to upload file via ftp by tsql in sql server?
December 11, 2008 at 7:45 am
I would entertain using
1. SSIS with built in FTP tasks
2. xp_cmdshell with external batch file (say ftp_upload.bat)
therefore you edit all FTP operations in the batch file, independent of T-SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply