T-SQL FTP using xp_cmdshell

Question

I am using the below code snippet to FTP simple text files from a Windows Server 2003 / SQL Server 2005 database to a BULL FTP site and am getting some corruption on the BULL end.

select @cmd = 'ftp -s:' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT

The text file contains SEP490067 but when this is opened on the BULL it appears like this ..S.E.P.4.9.0.0.6.7..

I have seen something like this before when sending files using an SSIS FTP component and switching the IsTransferAscii switch to True fixes the problem. The question I have is doe you have to set another commands line switch in the FTP command above to sent the files using ASCII rather than Binary?

Here is the fully TSQL

USE [JCB_QSmart]
GO
/****** Object:  StoredProcedure [dbo].[FTP_PutFile]    Script Date: 06/14/2012 08:56:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[FTP_PutFile]
@FTPServer  varchar(128) ,
@FTPUser    varchar(128) ,
@FTPPWD     varchar(128) ,
@FTPPath    varchar(128) ,
@FTPFileName    varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir    varchar(128),
    @ShowOutput as bit = 0
as

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 '                  + '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, NO_OUTPUT

    select id, ouputtmp = s from #a

Answer 1

Since you use the -s switch for ftp you should put the ascii-command in your workfile right after you connected to the FTP server.

EDIT:

So in your StoredProc you have to add the following line right before the line that contains the 'put ' command:

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

Code should include additional >

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