FTP from SQL Server

Andrew Zanevsky

This article demonstrates that SQL Server can FTP data directly to Web sites and other servers using plain vanilla T-SQL and standard command-prompt utilities.

File Transfer Protocol (FTP) is a powerful method of transferring files over the Internet. It allows you to download files from computers around the world or upload files from your own computer. In the past few years, arcane command-prompt FTP syntax has been efficiently superseded by GUI tools performing the same function transparent to the user. But you can also issue FTP commands from a command prompt of Windows NT or 9x.

The ability to publish files on the Internet might be very useful if you're building an application delivering your SQL Server data to the public. For example, you might want to refresh your company's price list on the Web site, or post most recent announcements on your intranet site. FTP might be a nice alternative to e-mail in certain cases. As long as you can write a stored procedure retrieving data in the right format, you should be able to push that file to any FTP server that you have access to.

For simple file uploading and downloading tasks, you don't have to install and configure Microsoft Internet Information Server (IIS) with your SQL Server. Instead, you can download and use my stored procedure called, naturally, ftp.

Executing command-prompt FTP
Let me start with simple FTP exercises for the benefit of readers unfamiliar with the process. We'll run FTP from a command prompt. It has nothing to do with SQL Server -- yet.

You can use command-prompt FTP interactively. Open a "DOS" window, then type FTP and press Enter, and you're at the FTP prompt. Optionally, you may specify a name or IP address of a remote FTP server. In that case, the program will prompt you for a user ID and password. After logging in to a remote FTP server, you can type commands, such as GET to download a file, PUT to upload one, CD to change directory on the FTP server, DIR to get a file directory listing, and so on. For a complete list of commands, you may enter HELP. Listing 1 shows a sample session with Microsoft's anonymous FTP site. User responses appear in italics.

Listing 1. A sample session with Microsoft's anonymous FTP site. 
  
 C:\>ftp ftp.microsoft.com 
 Connected to ftp.microsoft.com. 
 220 ftp Microsoft FTP Service (Version 4.0). 
 User (ftp.microsoft.com:(none)): anonymous 
 331 Anonymous access allowed, send identity (e-mail name) as password. 
 Password: my_name@my_company.com 
 230-This is FTP.MICROSOFT.COM 
 230-Please see the dirmap.txt file for 
 230-more information. 
 230 Anonymous user logged in. 
 ftp> dir dirmap.* 
 200 PORT command successful. 
 150 Opening ASCII mode data connection for /bin/ls. 
 -r-xr-xr-x   1 owner    group            7983 Jan 28 14:29 dirmap.htm 
 -r-xr-xr-x   1 owner    group            4333 Jan 28 14:28 dirmap.txt 
 226 Transfer complete. 
 142 bytes received in 0.03 seconds (4.73 Kbytes/sec) 
 ftp> quit 
 221 Thank you for using Microsoft Products! 
  
 C:\>

In order to automate the same session, you need to create a script file that can be "fed" to FTP instead of entering every command interactively. Suppose that you create a file SCRIPT.FTP in your current directory with the following contents:

 anonymous 
 my_name@my_company.com 
 dir dirmap.* 
 quit

To execute this script, you only have to enter this:

 ftp -s:script.ftp ftp.microsoft.com

Now, the same result comes back without prompting you for any additional information.

FTP security
Note that in previous examples, we've been using an FTP site here that allows anonymous access. This means that you can enter the user name of "anonymous" and provide virtually any password. They ask you to enter your e-mail address for the password, but there's no real restriction on what you type. Therefore, anybody can gain access to this public site. But many FTP servers are secure and require knowing a valid user ID and password to enter.

Corporations usually implement firewalls that hide their servers from the Internet. Outside access is routed through a firewall server that decides who's allowed to enter or to go outside. It acts as a gatekeeper. You might need to obtain a special user ID and password to get through the firewall to the outside world. Following is a sample FTP script that goes through the firewall. Items that you need to modify for your specific site and task are shown in italics.

 open firewall_server 
firewall_user
firewall_password 
 site quote ftp.microsoft.com 
 user 
anonymous
my_name@my_company.com
get dirmap.txt
... other FTP commands ... 
 quit

In this example, the OPEN command starts a connection to the firewall server. The user ID and password follow in the next two lines. Provided that user authentication succeeds, the SITE QUOTE command requests a connection to an outside FTP server. The USER command initiates the authentication process on the remote FTP server, and the next two lines supply the user ID and password for it. After that, you may enter commands that manipulate files on the FTP server. In this example, we use the GET command to download the file DIRMAP.txt to the local hard drive. The QUIT command ends the session.

ftp from T-SQL
Now that you know how to run FTP scripts, you can create a stored procedure that would do the same. Assuming that you've saved your SCRIPT.FTP file in the C:\TEMP directory, you could execute it as follows:

 exec master..xp_cmdshell  
      'ftp -s:c:\temp\script.ftp ftp.microsoft.com'

Most DBAs don't like storing information outside of their databases. Most network administrators have just the opposite view, but I feel more in control of the situation when all my scripts are inside of SQL Server stored procedures or tables. Stored procedure ftp that I offer you with this article executes an FTP script saved in a database table. Here's the source code, also available in the accompanying Download file:

 create proc ftp 
 -- Purpose: Execute an FTP script  
 -- Author:  Andrew Zanevsky, 21st Sentry, Inc. 
   @script_table sysname, 
   @column_name  sysname, 
   @ftp_cmd_line varchar(255) = ' ' 
 as 
 set nocount on 
 declare @line     varchar(255),  
         @cmd      varchar(255),  
         @pipe     varchar(2),  
         @ftp_file varchar(255) 
 select  @pipe = '>',  
         @ftp_file = '~~ftp_temp_' +  
             convert( varchar, @@spid ) + '.ftp' 
 create table #script (  
         line varchar(255) not null ) 
 exec( 'insert #script select ' + @column_name +  
       ' from ' + @script_table ) 
 declare script cursor  
 for select line from #script 
 open script  
 fetch script into @line 
 while @@fetch_status = 0 begin 
   select @cmd = 'echo ' + @line + @pipe + @ftp_file 
   exec master..xp_cmdshell @cmd 
   fetch script into @line 
   select @pipe = '>>' 
 end 
 close script 
 deallocate script 
 select @cmd = 'ftp -s:' + @ftp_file + ' ' +  
               @ftp_cmd_line 
 exec master..xp_cmdshell @cmd 
 go

The procedure takes three parameters:

@script_table specifies the name of a temporary or permanent table that contains FTP script.
@column_name specifies the name of a char or varchar type column of the @script_table that contains the script.
@ftp_cmd_line contains any FTP command line options to be entered after the script file name. It may be an FTP server address or command line switches. For example, you might find the v switch useful. It suppresses most of the remote FTP server prompts.

In order to execute the ftp procedure, you'll need to prepare a script table first. In the following example, I do that and also preserve the FTP output in a table (#ftp_result) for further parsing and analysis:

set nocount on create table #ftp_script ( script_line varchar(255) not null ) create table #ftp_result ( result_line varchar(255) null ) insert #ftp_script values ( 'anonymous' ) insert #ftp_script values ( 'name@co.com' ) insert #ftp_script values ( 'dir dirmap.*' ) insert #ftp_script values ( 'get dirmap.txt' ) insert #ftp_script values ( 'quit' ) insert #ftp_result ( result_line ) exec ftp '#ftp_script', 'script_line', 'ftp.microsoft.com' select * from #ftp_result drop table #ftp_script drop table #ftp_result

I used a temporary table to store my FTP script, but you might want to use permanent ones to save frequently executed scripts.

Generating simple HTML files
Using this procedure, you can create scheduled SQL Server jobs that extract data from a table into a file, and then FTP the file to an FTP server where it may be picked up by recipients or viewed through a browser by your users. For example, you could generate an HTML file with the list of author names from the pubs..authors table using the following query:

select '<H1>Authors</H1>' = '<BR>' + substring( au_fname + ' ' + au_lname, 1, 25 ) from pubs..authors

Granted, it takes more skills to generate more complex Web pages, but for trivial tasks, this might be a very practical technique.

Words of gratitude
I thank my colleague, consultant Chris Geraghty, for his help in preparing this article. His expertise in networking technologies is invaluable.

I'd also like to share with you some insightful ideas received from fellow DBAs. Mike Hotek has commented on my recent article about tempdb. He notes that one more scenario in which tempdb catalog tables might get locked by a single process is when you use the INSERT...EXEC... command to execute a stored procedure that creates temporary tables. The INSERT command begins an implicit transaction. CREATE TABLE inside of the procedure places locks on system tables. These locks are held until the whole procedure finishes and INSERT commits transaction.

I should also mention that Mike Hotek has recently registered a new Internet domain and is building his Web site (www.mssqlserver.com). Surprisingly, the domain name wasn't registered by Microsoft.

Richard Maw (read his insightful article, "Generating Database Scripts," in the February 1999 issue of SQL Server Professional) has noticed a bug in one of my stored procedures published in December 1998. Procedure sp_db_dev_map relies on sp_helpdb to obtain the list of databases. But standard sp_helpdb truncates database names to 24 characters. As a result, my procedure breaks on long database names. I could rewrite it to query system tables instead of using sp_helpdb, but Richard suggests a quick workaround. The following line in sp_db_dev_map:

if @x not like ' ' + @db + '%' begin

should be changed to:

if @x not like ' '+substring(@db,1,24)+'%' begin

Thank you all for reading and for contributing your ideas!

Download ZANE499.SQL

Andrew Zanevsky is a Microsoft SQL Server and Sybase DBA and database and data warehouse designer. He's a co-author of Transact-SQL Programming (O'Reilly & Associates) and a contributor to three other books. He's president of a consulting firm, AZ Databases, Inc., and partner of 21st Sentry, Inc. -- provider of remote DBA services. 708-609-8783, fax 847-419-0190, zanevsky@azdatabases.com.