SQL Server

Append to Existing File on FTP Server

See more FTP Examples

Uploads a file to the FTP server. If the remote file (on the FTP server) does not already exist, it is created. Otherwise the uploaded file is appended to the remote file. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ftp int
    EXEC @hr = sp_OACreate 'Chilkat.Ftp2', @ftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @ftp, 'Hostname', 'ftp.myftpserver.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'myUsername'
    EXEC sp_OASetProperty @ftp, 'Password', 'myPassword'

    -- Connect to the FTP server.
    EXEC sp_OAMethod @ftp, 'ConnectOnly', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Authenticate with the FTP server.
    EXEC sp_OAMethod @ftp, 'LoginAfterConnectOnly', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    DECLARE @localFilePath nvarchar(4000)
    SELECT @localFilePath = '/someDirectory/hamlet.xml'
    DECLARE @remoteFilename nvarchar(4000)
    SELECT @remoteFilename = 'hamlet.xml'

    -- Upload to the FTP server, creating the file if it does not yet exist
    -- on the FTP server, or appending to the remote file if it already exists.
    EXEC sp_OAMethod @ftp, 'AppendFile', @success OUT, @localFilePath, @remoteFilename
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @ftp


END
GO