SQL Server
SQL Server
SSH Remote Shell Multiple Commands
See more SSH Examples
Demonstrates how to start a shell on a remote SSH server, run a command, and retrieve the output, then run the next command, retrieve the output, etc.Chilkat SQL Server Downloads
-- 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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @ssh int
EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- This is the prompt we'll be expecting to find in
-- the output of the remote shell.
DECLARE @myPrompt nvarchar(4000)
SELECT @myPrompt = 'chilkat@ubuntu:~/workspace$'
DECLARE @cmdOutput nvarchar(4000)
-- Connect to an SSH server:
DECLARE @hostname nvarchar(4000)
DECLARE @port int
-- Hostname may be an IP address or hostname:
SELECT @hostname = 'www.some-ssh-server.com'
SELECT @port = 22
EXEC sp_OAMethod @ssh, 'Connect', @success OUT, @hostname, @port
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Wait a max of 10 seconds when reading responses..
EXEC sp_OASetProperty @ssh, 'IdleTimeoutMs', 10000
-- Authenticate using login/password:
EXEC sp_OAMethod @ssh, 'AuthenticatePw', @success OUT, 'myLogin', 'myPassword'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Open a session channel. (It is possible to have multiple
-- session channels open simultaneously.)
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
DECLARE @termType nvarchar(4000)
SELECT @termType = 'dumb'
DECLARE @widthInChars int
SELECT @widthInChars = 120
DECLARE @heightInChars int
SELECT @heightInChars = 40
-- Use 0 for pixWidth and pixHeight when the dimensions
-- are set in number-of-chars.
DECLARE @pixWidth int
SELECT @pixWidth = 0
DECLARE @pixHeight int
SELECT @pixHeight = 0
EXEC sp_OAMethod @ssh, 'SendReqPty', @success OUT, @channelNum, @termType, @widthInChars, @heightInChars, @pixWidth, @pixHeight
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Start a shell on the channel:
EXEC sp_OAMethod @ssh, 'SendReqShell', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Run the 1st command in the remote shell, which will be to
-- "cd" to a subdirectory.
EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'cd workspace' + CHAR(13) + CHAR(10), 'ansi'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Retrieve the output.
EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, @myPrompt, 'ansi', 1
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Display what we've received so far. This clears
-- the internal receive buffer, which is important.
-- After we send the command, we'll be reading until
-- the next command prompt. If the command prompt
-- is already in the internal receive buffer, it'll think it's
-- already finished...
EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, '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 @cmdOutput
-- Run the 2nd command in the remote shell, which will be
-- to "ls" the directory.
EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'ls' + CHAR(13) + CHAR(10), 'ansi'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Retrieve and display the output.
EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, @myPrompt, 'ansi', 1
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, '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 @cmdOutput
-- Start the final command in the remote shell. This example
-- will send a "ls -l" command to retrieve the long format directory listing.
EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'ls -l' + CHAR(13) + CHAR(10), 'ansi'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Send an EOF. This tells the server that no more data will
-- be sent on this channel. The channel remains open, and
-- the SSH client may still receive output on this channel.
EXEC sp_OAMethod @ssh, 'ChannelSendEof', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Read whatever output may already be available on the
-- SSH connection. ChannelReadAndPoll returns the number of bytes
-- that are available in the channel's internal buffer that
-- are ready to be "picked up" by calling GetReceivedText
-- or GetReceivedData.
-- A return value of -1 indicates failure.
-- A return value of -2 indicates a failure via timeout.
-- The ChannelReadAndPoll method waits
-- for data to arrive on the connection usingi the IdleTimeoutMs
-- property setting. Once the first data arrives, it continues
-- reading but instead uses the pollTimeoutMs passed in the 2nd argument:
-- A return value of -2 indicates a timeout where no data is received.
DECLARE @n int
DECLARE @pollTimeoutMs int
SELECT @pollTimeoutMs = 2000
EXEC sp_OAMethod @ssh, 'ChannelReadAndPoll', @n OUT, @channelNum, @pollTimeoutMs
IF @n < 0
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Close the channel:
EXEC sp_OAMethod @ssh, 'ChannelSendClose', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Perhaps we did not receive all of the commands output.
-- To make sure, call ChannelReceiveToClose to accumulate any remaining
-- output until the server's corresponding "channel close" is received.
EXEC sp_OAMethod @ssh, 'ChannelReceiveToClose', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, '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 @cmdOutput
-- Disconnect
EXEC sp_OAMethod @ssh, 'Disconnect', NULL
EXEC @hr = sp_OADestroy @ssh
END
GO