/**********************************************************************************************************************************************
	Name:		ftp_sp
	Description:	Sends a file to an FTP site using the included parameters.
	Author:		PJohnson, 7/20/2005
	Parameters:
			@site		Site to send the file; can be a URL or an IP address
			@username	User name to login to FTP site
			@password	Password for FTP site user
			@file_path	Path in which to find the file to send
			@file_name	Name of file to send
			@dest_path	Destination directory on remote FTP site; set to '' if default directory to be used

	Examples:	exec ftp_sp 'ftp.domain.com', 'User1', 'Password1', 'c:\', 'test.txt', 'incoming\'
			exec ftp_sp '65.244.244.25', 'User1', 'Password1', 'c:\', 'test.txt', ''

	Notes:		This stored procedure must be modified for each server it is run on.  This includes the 
			server name, user, and password.  It may be possible to just use a trusted connection.
			Cannot use NULLS for users and passwords.

	CAUTION:	Be careful when including passwords in scripts that can be read by other users!

-- drop proc ftp_sp
***********************************************************************************************************************************************/


CREATE procedure ftp_sp 
	@site varchar(255), 
	@username varchar(50),
	@password varchar(40),
	@file_path varchar(255),
	@file_name varchar(100),
	@dest_path varchar(255) = ''

as 

set nocount on

declare @sql_server varchar(50),
	@sql_user_name varchar(50),
	@sql_password varchar(40),
	@script_file_path varchar(255),
	@script_file_name varchar(100),
	@cmd varchar(1000)

set @sql_server = 'SQLSERVER'
set @sql_user_name = 'SQLUser'
set @sql_password = 'SQLPass'
set @script_file_path = 'c:\anydir\'		-- Use for temporarily storing an ftp script file
set @script_file_name = '_ftpscr.txt'		-- Use for temporarily storing an ftp script file

if right(@script_file_path, 1) <> '\' set @script_file_path = @script_file_path + '\'

if right(@file_path, 1) <> '\' set @file_path = @file_path + '\'



create table ##output (seq_id int identity(1,1), field1 varchar(255))


insert ##output (field1)
select 'open ' + @site
union all
select @username
union all
select @password
union all
select 'put ' + @file_path + @file_name + ' ' + @dest_path + @file_name
union all
select 'quit'

set @cmd = 'bcp "select field1 from ##output order by seq_id" queryout ' + @script_file_path + @script_file_name + ' -c -S"' + @sql_server + '" -U"' + @sql_user_name + '" -P"' + @sql_password + '"'
exec master..xp_cmdshell @cmd, no_output

--select * from ##output

drop table ##output
set @cmd = 'ftp -s:' + @script_file_path + @script_file_name
exec master..xp_cmdshell @cmd
GO