SQL Server
SQL Server
Azure List Storage Accounts
See more Azure Storage Accounts Examples
Demonstrates how to list Azure storage accounts.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 @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Load an OAuth2 access token previously fetched by this example: Get Azure OAuth2 Access Token
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/azureToken.json'
-- Assuming success..
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
EXEC sp_OAGetProperty @http, 'AuthToken', @sTmp0 OUT
PRINT 'AuthToken: ' + @sTmp0
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://management.azure.com/subscriptions/{subscriptionId}/providers/Microsoft.Storage/storageAccounts?api-version=2018-11-01'
DECLARE @jsonResp nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @jsonResp OUT, @url
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResp
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @json
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "value": [
-- {
-- "sku": {
-- "name": "Standard_LRS",
-- "tier": "Standard"
-- },
-- "kind": "Storage",
-- "id": "/subscriptions/6c42643b-ebef-45e0-b917-b3583b84a57f/resourceGroups/gchilkat/providers/Microsoft.Storage/storageAccounts/chilkat",
-- "name": "chilkat",
-- "type": "Microsoft.Storage/storageAccounts",
-- "location": "eastus",
-- "tags": {},
-- "properties": {
-- "networkAcls": {
-- "bypass": "AzureServices",
-- "virtualNetworkRules": [
-- ],
-- "ipRules": [
-- ],
-- "defaultAction": "Allow"
-- },
-- "supportsHttpsTrafficOnly": true,
-- "encryption": {
-- "services": {
-- "file": {
-- "enabled": true,
-- "lastEnabledTime": "2017-12-28T11:02:10.6840887Z"
-- },
-- "blob": {
-- "enabled": true,
-- "lastEnabledTime": "2017-12-28T11:02:10.6840887Z"
-- }
-- },
-- "keySource": "Microsoft.Storage"
-- },
-- "provisioningState": "Succeeded",
-- "creationTime": "2016-04-18T22:57:36.5377065Z",
-- "primaryEndpoints": {
-- "blob": "https://chilkat.blob.core.windows.net/",
-- "queue": "https://chilkat.queue.core.windows.net/",
-- "table": "https://chilkat.table.core.windows.net/",
-- "file": "https://chilkat.file.core.windows.net/"
-- },
-- "primaryLocation": "eastus",
-- "statusOfPrimary": "available"
-- }
-- }
-- ]
-- }
--
DECLARE @i int
DECLARE @count_i int
DECLARE @skuName nvarchar(4000)
DECLARE @skuTier nvarchar(4000)
DECLARE @kind nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @name nvarchar(4000)
DECLARE @v_type nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @propertiesNetworkAclsBypass nvarchar(4000)
DECLARE @propertiesNetworkAclsDefaultAction nvarchar(4000)
DECLARE @propertiesSupportsHttpsTrafficOnly int
DECLARE @propertiesEncryptionServicesFileEnabled int
DECLARE @propertiesEncryptionServicesFileLastEnabledTime nvarchar(4000)
DECLARE @propertiesEncryptionServicesBlobEnabled int
DECLARE @propertiesEncryptionServicesBlobLastEnabledTime nvarchar(4000)
DECLARE @propertiesEncryptionKeySource nvarchar(4000)
DECLARE @propertiesProvisioningState nvarchar(4000)
DECLARE @propertiesCreationTime nvarchar(4000)
DECLARE @propertiesPrimaryEndpointsBlob nvarchar(4000)
DECLARE @propertiesPrimaryEndpointsQueue nvarchar(4000)
DECLARE @propertiesPrimaryEndpointsTable nvarchar(4000)
DECLARE @propertiesPrimaryEndpointsFile nvarchar(4000)
DECLARE @propertiesPrimaryLocation nvarchar(4000)
DECLARE @propertiesStatusOfPrimary nvarchar(4000)
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'value'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @skuName OUT, 'value[i].sku.name'
EXEC sp_OAMethod @json, 'StringOf', @skuTier OUT, 'value[i].sku.tier'
EXEC sp_OAMethod @json, 'StringOf', @kind OUT, 'value[i].kind'
EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'value[i].id'
EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'value[i].name'
EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'value[i].type'
EXEC sp_OAMethod @json, 'StringOf', @location OUT, 'value[i].location'
EXEC sp_OAMethod @json, 'StringOf', @propertiesNetworkAclsBypass OUT, 'value[i].properties.networkAcls.bypass'
EXEC sp_OAMethod @json, 'StringOf', @propertiesNetworkAclsDefaultAction OUT, 'value[i].properties.networkAcls.defaultAction'
EXEC sp_OAMethod @json, 'BoolOf', @propertiesSupportsHttpsTrafficOnly OUT, 'value[i].properties.supportsHttpsTrafficOnly'
EXEC sp_OAMethod @json, 'BoolOf', @propertiesEncryptionServicesFileEnabled OUT, 'value[i].properties.encryption.services.file.enabled'
EXEC sp_OAMethod @json, 'StringOf', @propertiesEncryptionServicesFileLastEnabledTime OUT, 'value[i].properties.encryption.services.file.lastEnabledTime'
EXEC sp_OAMethod @json, 'BoolOf', @propertiesEncryptionServicesBlobEnabled OUT, 'value[i].properties.encryption.services.blob.enabled'
EXEC sp_OAMethod @json, 'StringOf', @propertiesEncryptionServicesBlobLastEnabledTime OUT, 'value[i].properties.encryption.services.blob.lastEnabledTime'
EXEC sp_OAMethod @json, 'StringOf', @propertiesEncryptionKeySource OUT, 'value[i].properties.encryption.keySource'
EXEC sp_OAMethod @json, 'StringOf', @propertiesProvisioningState OUT, 'value[i].properties.provisioningState'
EXEC sp_OAMethod @json, 'StringOf', @propertiesCreationTime OUT, 'value[i].properties.creationTime'
EXEC sp_OAMethod @json, 'StringOf', @propertiesPrimaryEndpointsBlob OUT, 'value[i].properties.primaryEndpoints.blob'
EXEC sp_OAMethod @json, 'StringOf', @propertiesPrimaryEndpointsQueue OUT, 'value[i].properties.primaryEndpoints.queue'
EXEC sp_OAMethod @json, 'StringOf', @propertiesPrimaryEndpointsTable OUT, 'value[i].properties.primaryEndpoints.table'
EXEC sp_OAMethod @json, 'StringOf', @propertiesPrimaryEndpointsFile OUT, 'value[i].properties.primaryEndpoints.file'
EXEC sp_OAMethod @json, 'StringOf', @propertiesPrimaryLocation OUT, 'value[i].properties.primaryLocation'
EXEC sp_OAMethod @json, 'StringOf', @propertiesStatusOfPrimary OUT, 'value[i].properties.statusOfPrimary'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @json
END
GO