Question
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
Replies
Ch. Rajen Singh on Sat, 12 Apr 2014 14:17:40
You said you are uploading XML file whereas in the TSQL you have assign as text file i.e @SourceFile = 'test.txt'. Are you sure that you are copying XML file ? What is the size of the source file "test.txt" ? I am sure it is 0 KB. That is what you have found in FTP site.