(SQL Server) SSH Parallel Remote Commands on Single Server

Shows how to execute multiple commands in parallel on a single SSH server and retrieve the command output for each.

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 @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

    -- Start several commands on the server.
    DECLARE @channel1 int
    EXEC sp_OAMethod @ssh, 'QuickCmdSend', @channel1 OUT, 'df'
    IF @channel1 < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    DECLARE @channel2 int
    EXEC sp_OAMethod @ssh, 'QuickCmdSend', @channel2 OUT, 'date'
    IF @channel2 < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    DECLARE @channel3 int
    EXEC sp_OAMethod @ssh, 'QuickCmdSend', @channel3 OUT, 'echo hello world'
    IF @channel3 < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Now collect the results of each command.
    DECLARE @pollTimeoutMs int
    SELECT @pollTimeoutMs = 50
    DECLARE @numFinished int
    SELECT @numFinished = 0
    WHILE @numFinished < 3
      BEGIN
        -- Check to see if anything has finished.
        -- QuickCmdCheck returns -1 if there are no errors and nothing else finished
        -- QuickCmdCheck returns -2 if there was an error (such as a lost connection)
        -- QuickCmdCheck returns a channel number if a channel finished.
        DECLARE @channel int
        EXEC sp_OAMethod @ssh, 'QuickCmdCheck', @channel OUT, @pollTimeoutMs
        IF @channel = -2
          BEGIN
            EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @ssh
            RETURN
          END
        IF @channel >= 0
          BEGIN


            PRINT '---- channel ' + @channel + ' finished ----'
            EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channel, 'ansi'
            PRINT @sTmp0
            SELECT @numFinished = @numFinished + 1
          END
      END

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

    -- 	---- channel 105 finished ----
    -- 	hello world
    -- 
    -- 	---- channel 104 finished ----
    -- 	Thu Dec 22 17:43:58 CST 2016
    -- 
    -- 	---- channel 103 finished ----
    -- 	Filesystem    512-blocks      Used  Available Capacity  iused     ifree %iused  Mounted on
    -- 	/dev/disk2    2176716032 265739728 1910464304    13% 33281464 238808038   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

    EXEC @hr = sp_OADestroy @ssh


END
GO