SQL Server
SQL Server
EC2 Check Instance Status
See more Amazon EC2 Examples
Demonstrates how to send a request to check the status of Amazon EC2 instances.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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Connect to the Amazon AWS server.
-- such as https://ec2.amazonaws.com/
DECLARE @bTls int
SELECT @bTls = 1
DECLARE @port int
SELECT @port = 443
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'ec2.us-west-2.amazonaws.com', @port, @bTls, @bAutoReconnect
-- Provide AWS credentials for the REST call.
DECLARE @authAws int
EXEC @hr = sp_OACreate 'Chilkat.AuthAws', @authAws OUT
EXEC sp_OASetProperty @authAws, 'AccessKey', 'AWS_ACCESS_KEY'
EXEC sp_OASetProperty @authAws, 'SecretKey', 'AWS_SECRET_KEY'
-- the region should match the region part of the domain passed to the REST connect method (above).
EXEC sp_OASetProperty @authAws, 'Region', 'us-west-2'
EXEC sp_OASetProperty @authAws, 'ServiceName', 'ec2'
EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Action', 'DescribeInstanceStatus'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Version', '2016-11-15'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'InstanceId.1', 'i-999719995399c9999'
-- If you have additional instances to check, then add them with .2, .3, etc.
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'InstanceId.2', 'i-999719995399c0000'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'InstanceId.3', 'i-999719995399c0111'
-- ...
-- Instead of checking specific instances, you can check all instances by
-- sending the IncludeAllInstances param
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'IncludeAllInstances', 'true'
DECLARE @responseXml nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseXml OUT, 'GET', '/'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authAws
RETURN
END
-- A successful response will have a status code equal to 200.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response body: ' + @responseXml
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authAws
RETURN
END
-- Examine the successful XML response.
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- Here is a sample response:
-- Use this online tool to generate parsing code from sample XML:
-- Generate Parsing Code from XML
-- <DescribeInstanceStatusResponse xmlns="http://ec2.amazonaws.com/doc/2016-11-15/">
-- <requestId>3be1508e-c444-4fef-89cc-0b1223c4f02fEXAMPLE</requestId>
-- <instanceStatusSet>
-- <item>
-- <instanceId>i-1234567890abcdef0</instanceId>
-- <availabilityZone>us-east-1d</availabilityZone>
-- <instanceState>
-- <code>16</code>
-- <name>running</name>
-- </instanceState>
-- <systemStatus>
-- <status>impaired</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>failed</status>
-- <impairedSince>YYYY-MM-DDTHH:MM:SS.000Z</impairedSince>
-- </item>
-- </details>
-- </systemStatus>
-- <instanceStatus>
-- <status>impaired</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>failed</status>
-- <impairedSince>YYYY-MM-DDTHH:MM:SS.000Z</impairedSince>
-- </item>
-- </details>
-- </instanceStatus>
-- <eventsSet>
-- <item>
-- <code>instance-retirement</code>
-- <description>The instance is running on degraded hardware</description>
-- <notBefore>YYYY-MM-DDTHH:MM:SS+0000</notBefore>
-- <notAfter>YYYY-MM-DDTHH:MM:SS+0000</notAfter>
-- </item>
-- </eventsSet>
-- </item>
-- <item>
-- <instanceId>i-0598c7d356eba48d7</instanceId>
-- <availabilityZone>us-east-1d</availabilityZone>
-- <instanceState>
-- <code>16</code>
-- <name>running</name>
-- </instanceState>
-- <systemStatus>
-- <status>ok</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>passed</status>
-- </item>
-- </details>
-- </systemStatus>
-- <instanceStatus>
-- <status>ok</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>passed</status>
-- </item>
-- </details>
-- </instanceStatus>
-- <eventsSet>
-- <item>
-- <code>instance-reboot</code>
-- <description>The instance is scheduled for a reboot</description>
-- <notBefore>YYYY-MM-DDTHH:MM:SS+0000</notBefore>
-- <notAfter>YYYY-MM-DDTHH:MM:SS+0000</notAfter>
-- </item>
-- </eventsSet>
-- </item>
-- <item>
-- <instanceId>i-0987654321abcdef0</instanceId>
-- <availabilityZone>us-east-1d</availabilityZone>
-- <instanceState>
-- <code>16</code>
-- <name>running</name>
-- </instanceState>
-- <systemStatus>
-- <status>ok</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>passed</status>
-- </item>
-- </details>
-- </systemStatus>
-- <instanceStatus>
-- <status>ok</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>passed</status>
-- </item>
-- </details>
-- </instanceStatus>
-- </item>
-- <item>
-- <instanceId>i-0598c7d356eba48d8</instanceId>
-- <availabilityZone>us-east-1d</availabilityZone>
-- <instanceState>
-- <code>16</code>
-- <name>running</name>
-- </instanceState>
-- <systemStatus>
-- <status>ok</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>passed</status>
-- </item>
-- </details>
-- </systemStatus>
-- <instanceStatus>
-- <status>insufficient-data</status>
-- <details>
-- <item>
-- <name>reachability</name>
-- <status>insufficient-data</status>
-- </item>
-- </details>
-- </instanceStatus>
-- </item>
-- </instanceStatusSet>
-- </DescribeInstanceStatusResponse>
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authAws
EXEC @hr = sp_OADestroy @xml
END
GO