(SQL Server) SSH Execute Remote Commands

Shows how to execute a command on an SSH server and retrieve the command output.

Note: This example requires Chilkat v9.5.0.65 or greater.

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
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes Chilkat SSH/SFTP to have been previously unlocked.
    -- See Unlock SSH for sample code.

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

    DECLARE @port int
    SELECT @port = 22
    DECLARE @success int
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'the-ssh-server.com', @port
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

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

    -- Send some commands and get the output.
    DECLARE @strOutput nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'df', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- df ----'

    PRINT @strOutput

    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'echo hello world', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- echo hello world ----'

    PRINT @strOutput

    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'date', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- date ----'

    PRINT @strOutput

    -- --------------
    -- Sample output:

    -- 	---- df ----
    -- 	Filesystem    512-blocks      Used  Available Capacity  iused     ifree %iused  Mounted on
    -- 	/dev/disk2    2176716032 265736304 1910467728    13% 33281036 238808466   12%   /
    -- 	devfs                382       382          0   100%      662         0  100%   /dev
    -- 	map -hosts             0         0          0   100%        0         0  100%   /net
    -- 	map auto_home          0         0          0   100%        0         0  100%   /home
    -- 	/dev/disk3s2      374668    374668          0   100%    93665         0  100%   /Volumes/Google Chrome
    -- 
    -- 	---- echo hello world ----
    -- 	hello world
    -- 
    -- 	---- date ----
    -- 	Thu Dec 22 17:19:32 CST 2016

    EXEC @hr = sp_OADestroy @ssh


END
GO