SQL Server

Copy Local File Attributes to a Remote SFTP File

Demonstrates the Chilkat SFtp.CopyFileAttr method, which copies supported timestamps and attributes from a local file to a remote item. The arguments are the local file, the remote file (a path or an open handle), and isHandle indicating which form the second argument takes.

Note: This example uses a relative local path, which is resolved against the application's current working directory. Absolute local paths may also be used. Supply the path appropriate to your own environment.

Background: An upload transfers a file's bytes but not necessarily its metadata, so the remote copy may end up with the upload time as its modified time. CopyFileAttr propagates the local file's timestamps and attributes to the remote file, which matters for backups and mirrors where preserving modification times is important — and for incremental sync logic that compares timestamps to decide what changed. The isHandle flag lets you target either a remote path or a file you already have open.

Chilkat SQL Server Downloads

Download Chilkat for SQL 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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the SFtp.CopyFileAttr method, which copies supported timestamps and attributes
    --  from a local file to a remote item.  The 1st argument is the local file, the 2nd is the
    --  remote file (a path or an open handle), and the 3rd (isHandle) indicates which.

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

    --  Connect, authenticate, and initialize the SFTP subsystem.
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, 'sftp.example.com', @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySshPassword'

    EXEC sp_OAMethod @sftp, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Upload a file first.
    EXEC sp_OAMethod @sftp, 'UploadFileByName', @success OUT, 'subdir/report.pdf', 'qa_data/report.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Copy the local file's timestamps and attributes onto the remote file.  Because the 2nd
    --  argument is a remote path (not an open handle), isHandle is 0.
    DECLARE @isHandle int
    SELECT @isHandle = 0
    EXEC sp_OAMethod @sftp, 'CopyFileAttr', @success OUT, 'qa_data/report.pdf', 'subdir/report.pdf', @isHandle
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    PRINT 'Attributes copied.'

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp


END
GO