SQL Server

Get Text Received on the SSH stderr Buffer

Demonstrates the Chilkat Ssh.GetReceivedStderrText method, which decodes and returns the text accumulated in a channel's separate stderr buffer. The first argument is the channel number and the second is the charset. The stderr buffer is cleared after the text is returned.

Background: This is only meaningful when StderrToStdout is false, which keeps error output in its own buffer instead of merging it into the normal receive buffer. Separating the two lets a program parse a command's real output cleanly while still capturing diagnostics — important because many Unix tools write warnings to stderr that would otherwise corrupt the parsed result. With merging enabled, this buffer stays empty and the text appears via GetReceivedText instead.

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.GetReceivedStderrText method, which decodes and returns the text in a
    --  channel's separate stderr buffer.  The 1st argument is the channel number and the 2nd is the
    --  charset.  The stderr buffer is cleared after the text is returned.

    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

    --  Keep stderr in its own buffer rather than merging it into the normal receive buffer.
    EXEC sp_OASetProperty @ssh, 'StderrToStdout', 0

    DECLARE @channelNum int
    EXEC sp_OAMethod @ssh, 'OpenSessionChannel', @channelNum OUT
    IF @channelNum < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Run a command that writes to both stdout and stderr.
    EXEC sp_OAMethod @ssh, 'SendReqExec', @success OUT, @channelNum, 'ls /etc/hosts /no/such/path'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  IMPORTANT: Set a read timeout.  ReadTimeoutMs defaults to 0, which means no limit -- without
    --  it, this call waits forever if the server never sends channel close.
    EXEC sp_OASetProperty @ssh, 'ReadTimeoutMs', 15000

    EXEC sp_OAMethod @ssh, 'ChannelReceiveToClose', @success OUT, @channelNum
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    DECLARE @stdoutText nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @stdoutText OUT, @channelNum, '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 'STDOUT: ' + @stdoutText

    DECLARE @stderrText nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedStderrText', @stderrText OUT, @channelNum, '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 'STDERR: ' + @stderrText

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO