SQL Server

SCP Sync Tree Upload

See more SCP Examples

Synchronize local and remote directory trees by uploading newer or missing files to the remote server. 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 @ssh int
    EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Hostname may be an IP address or hostname:
    DECLARE @hostname nvarchar(4000)
    SELECT @hostname = 'www.some-ssh-server.com'
    DECLARE @port int
    SELECT @port = 22

    -- Connect to an SSH server:
    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
    -- in our SCP object.
    DECLARE @scp int
    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

    -- The remoteRoot is relative to the HOME directory of the SSH user account.
    DECLARE @remoteRoot nvarchar(4000)
    SELECT @remoteRoot = 'workspace/projectB'
    DECLARE @localRoot nvarchar(4000)
    SELECT @localRoot = 'c:/aaworkarea/scp/workspace/projectB'

    -- Upload synchronization modes:
    -- mode=0: Upload all files
    -- mode=1: Upload all files that do not exist on the FTP server.
    -- mode=2: Upload newer or non-existant files.
    -- mode=3: Upload only newer files.  If a file does not already exist on the FTP server, it is not uploaded.
    -- mode=4: transfer missing files or files with size differences.
    -- mode=5: same as mode 4, but also newer files.

    -- Additional files and directores can be excluded by setting the SyncMustNotMatch property.
    -- The SyncMustNotMatch patterns apply only to the final filename or directory part of a path.
    -- If a directory matches, then it will not be traversed in a recursive traversal.
    -- 
    EXEC sp_OASetProperty @scp, 'SyncMustNotMatch', '*.o;*.obj;*.log; temp'

    -- The SyncMustMatch property can be set to restrict the files uploaded to only those
    -- matching at least one pattern in a set.
    EXEC sp_OASetProperty @scp, 'SyncMustMatch', '*.cpp; *.h'

    -- Do the recursive sync to upload newer or non-existant files:
    DECLARE @bRecurse int
    SELECT @bRecurse = 1
    DECLARE @mode int
    SELECT @mode = 2
    EXEC sp_OAMethod @scp, 'SyncTreeUpload', @success OUT, @localRoot, @remoteRoot, @mode, @bRecurse
    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

    -- The files actually uploaded can be examined in the SyncedFiles property.
    -- It is a string property that contains the relative path, one per line, of each
    -- file uploaded.

    PRINT 'Files uploaded: '
    EXEC sp_OAGetProperty @scp, 'SyncedFiles', @sTmp0 OUT
    PRINT @sTmp0


    PRINT '----'

    PRINT 'SCP sync upload success.'

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

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


END
GO