SQL Server

Run a Single SSH Command and Get the Output

Demonstrates the Chilkat Ssh.QuickCommand method, which runs one noninteractive remote command and returns its stdout as text. The first argument is the command and the second is the charset used to decode the output. Internally it opens a session channel, sends an exec request, and receives the output through EOF.

Background: This collapses the whole open-channel / exec / receive / retrieve sequence into a single call, and it is the right choice for the common case of "run one command and give me the output." Because no PTY is involved the output is clean and parseable, with no prompt or echoed input. Reach for the individual channel methods only when you need stderr separately, the exit status, or an interactive session.

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

    --  Demonstrates the Ssh.QuickCommand method, which runs one noninteractive remote command and
    --  returns its stdout as text.  The 1st argument is the command and the 2nd is the charset used
    --  to decode the output.  Internally it opens a session channel, sends an exec request, and
    --  receives the output through EOF.

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

    DECLARE @sshPort int
    SELECT @sshPort = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'ssh.example.com', @sshPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        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 @ssh, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Run a single command and collect its output in one call.
    DECLARE @output nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'QuickCommand', @output OUT, 'uname -a', 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT @output

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO