Problem with uploading file to FTP using SQL

RPetar on Tue, 25 Mar 2014 09:27:13

Hi people,I am trying to upload a local xml file to ftp server using procedure found on internet:

/****** Object:  StoredProcedure [dbo].[FtpPutFile]    Script Date: 03/25/2014 10:07:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[FtpPutFile]
	-- Add the parameters for the stored procedure here
@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)

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

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

I get that file on FTP but its empty (0 kb).

Somebody know why?

Thanks