SQL Server

Send an SSH IGNORE Message over SFTP

Demonstrates the Chilkat SFtp.SendIgnore method, which sends an SSH IGNORE message. It takes no arguments. The server sends no reply, but a successful send helps confirm the connection is still writable. No SFTP file handle is required.

Background: The SSH IGNORE message is discarded by the peer by design; its value is as a lightweight keep-alive or liveness probe. During a long-idle SFTP session — for instance while waiting between scheduled transfers — a periodic SendIgnore both keeps the connection from being dropped for inactivity and proves the link is still usable, without touching any file.

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.SendIgnore method, which sends an SSH IGNORE message.  It takes no
    --  arguments.  The server sends no reply, but a successful send helps confirm the connection is
    --  still writable.  No SFTP file handle is required.

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

    --  Step 1: Connect the SSH transport.  Port 22 is the usual port.
    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

    --  Step 3: Open the SFTP subsystem.  This must be done after authentication and before any
    --  file or directory operations.
    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

    --  Send an IGNORE message to verify the connection is still writable.
    EXEC sp_OAMethod @sftp, 'SendIgnore', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    PRINT 'IGNORE message sent.'

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp


END
GO