Stored Procedure to delete remote file using FTP

Question

Post reply

Stored Procedure to delete remote file using FTP

Cory Slaney

SSCrazy

Points: 2459
More actions
July 19, 2005 at 7:18 am

#68164

Is there a way to create a stored procedure to connect to a FTP server to delete a file? Please provide code samples. Any help would be greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Osoba SSCommitted Points: 1964 More actions · Answer 1

Provided you have permissions to delete:

This script came off this site, not sure who the original author was...

declare @foldername varchar(150)

decalre @DOSCommand varchar(150)

set @foldername='\\SERVERNAME\FTP\Folderpath\'

--check whether the user supplied a '\' in the dir name

if not(right(@foldername,1)='\')

set @foldername=@foldername+'\'

set @doscommand ='del /Q' + "" + @foldername + '*.xls'+""

(note I used the example of an Excel filetype with a wildcard for the filename)

exec master..xp_cmdshell @DOScommand

..I'll reply back to this thread with the original script w/author name when I find it.

Osoba SSCommitted Points: 1964 More actions · Answer 2

Original article:

http://www.sqlservercentral.com/columnists/hji/usingxp_cmdshell.asp

Cory Slaney SSCrazy Points: 2459 More actions · Answer 3

I wanted to open an FTP connection , delete the file and then close the FTP connection. Your example uses a mapped drive....is there way to do the same thing using FTP?

Brian Cidern-192506 SSC Journeyman Points: 88 More actions · Answer 4

Here's something I'd used some time ago. It was for a DTS package, so some modifications will be needed. Get creative...

1. Create a batch file that'll accept an input parameter.

In the batch file, call the FTP command with the -s parameter. The -s parameter tells the command to use an external command file.

The batch file should look something like:

FTP -s %1

2. In your PROC, generate a string with the commands that you'd use for the FTP session. It should look something like:

OPEN <IP ! FTP SITE>

EXIT

3. Export that string to a text file (may be easiest to export it to the same directory as the Batch file. Take a look at the SP_OACreate procedure with the "Scripting.FileSystemObject"

4. Generate another string containing the path to the batch file, followed by a space, and then the name of the exported file.

5. Execute that string via xp_cmdshell

6. Finally, delete the command file that was just created (as it contains logon credentials)

NOTE: You may find it a bit easier to implement this in DTS package. In fact, if the XP_CMDSHELL is not available to you, you might not have another choice.

Good Luck!

Brian

Cory Slaney SSCrazy Points: 2459 More actions · Answer 5

Thanks Brian. I will give that a try. Much appreciated!