(SQL Server) SCP Upload to a Specific Remote Directory

Demonstrates how to upload a file using the SCP protocol (Secure Copy Protocol over SSH). The file is uploaded to a specific remote directory. It can be either a path relative to the HOME directory of the SSH user account, or an absolute path on the remote filesystem. Obviously, in both cases the SSH user account must have permission to create or overwrite a file in the specified directory.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ssh int
    -- Use "Chilkat_9_5_0.Ssh" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to an SSH server:
    DECLARE @hostname nvarchar(4000)

    DECLARE @port int

    -- Hostname may be an IP address or hostname:
    SELECT @hostname = 'www.some-ssh-server.com'
    SELECT @port = 22

    DECLARE @success int
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, @hostname, @port
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Wait a max of 5 seconds when reading responses..
    EXEC sp_OASetProperty @ssh, 'IdleTimeoutMs', 5000

    -- Authenticate using login/password:
    EXEC sp_OAMethod @ssh, 'AuthenticatePw', @success OUT, 'myLogin', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Once the SSH object is connected and authenticated, we use it
    -- as the underlying transport in our SCP object.
    DECLARE @scp int
    -- Use "Chilkat_9_5_0.Scp" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Scp', @scp OUT

    EXEC sp_OAMethod @scp, 'UseSsh', @success OUT, @ssh
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @scp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @scp
        RETURN
      END

    -- This uploads a file to the "uploads/text" directory relative to the HOME
    -- directory of the SSH user account.  For example, if the HOME directory is /home/chilkat,
    -- then this uploads to /home/chilkat/uploads/text/test.txt
    -- Note: The remote target directory must already exist on the SSH server.
    DECLARE @remotePath nvarchar(4000)
    SELECT @remotePath = 'uploads/text/test.txt'
    DECLARE @localPath nvarchar(4000)
    SELECT @localPath = '/home/bob/test.txt'
    EXEC sp_OAMethod @scp, 'UploadFile', @success OUT, @localPath, @remotePath
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @scp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @scp
        RETURN
      END

    -- This upload fully specifies the absolute remote path.
    SELECT @remotePath = '/home/chilkat/junk/abc/hamlet.xml'
    SELECT @localPath = '/home/bob/hamlet.xml'
    EXEC sp_OAMethod @scp, 'UploadFile', @success OUT, @localPath, @remotePath
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @scp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @scp
        RETURN
      END


    PRINT 'SCP upload file success.'

    -- Disconnect
    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh
    EXEC @hr = sp_OADestroy @scp


END
GO