sp to ftp delete file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sp to ftp delete file

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-12 : 11:52:03

Hi, all,

I need to create a sp that will ftp delete a file on remote server given the file name.

I have modified Nigel's ftp_get_file as follow.
(http://www.nigelrivett.net/)

Well, when I run the testing script within the /**/ with the real login info, the delete did not happen.

And, if I used the cmd prompt to do the same, the file did get deleted.

Anybody has any idea? Thanks!

--Sp
if exists (select * from sysobjects where id = object_id(N'[dbo].[ps_ftp_DeleteFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ps_ftp_DeleteFile]
GO

Create procedure ps_ftp_DeleteFile
@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
/*
exec ps_ftp_DeleteFile
@FTPServer = 'ftp.myftp.net' ,
@FTPUser = 'Admin' ,
@FTPPWD = 'pwd' ,
@FTPPath = '/test/' ,
@FTPFileName = 'impSKU07090503.txt' ,
@SourcePath = '' ,
@SourceFile = '' ,
@workdir = 'c:\temp\'
*/

declare @cmd varchar(1000)
declare @workfilename varchar(128)

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 ' + 'delete ' + @FTPPath + @FTPFileName
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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-12 : 20:33:52

Did you run the commands that are in the file from the command prompt?
The SP should give the output from running the file - what did it say?
try
cd @FTPPath
del @FTPFileName

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-13 : 09:27:09

Thank you!

I changed part of my code to next then it worked!

select @cmd = 'echo ' + 'cd ' + @FTPPath
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'del ' + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources