SQL Server
SQL Server
Microsoft Graph -- List Users
See more Microsoft Graph Examples
Retrieve a list of Microsoft Graph user objects.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
-- ------------------------------------------------------------------------------------------------------
-- See an easier way to do OAuth2 client credentials:
-- Example using Automatic OAuth2 Client Credentials
-- ------------------------------------------------------------------------------------------------------
-- Get an access token with the required scope using client credentials...
-- See How to Create Microsoft Graph App (in Azure Portal) for Client Credentials Authentication
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'CLIENT_SECRET'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', 'CLIENT_ID'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'scope', 'https://graph.microsoft.com/.default'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'
-- Use your own tenant ID, for example 4d8fdd66-66d1-43b0-ae5c-e31b4b7de5cd
DECLARE @url nvarchar(4000)
SELECT @url = 'https://login.microsoftonline.com/TENANT_ID/oauth2/v2.0/token'
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, @url, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @statusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT
PRINT 'Response status code = ' + @statusCode
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
-- -----------------------------------------------------
-- Use the access token obtained from above.
-- Note: We don't need to re-fetch a new access token every time. An access token is valid
-- for some amount of time, typically an hour (3600 seconds)
-- Use your previously obtained access token here:
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
EXEC sp_OAGetProperty @http, 'AuthToken', @sTmp0 OUT
PRINT 'access token: ' + @sTmp0
DECLARE @sbResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/users', @sbResponse
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbResponse
RETURN
END
EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Status code = ' + @iTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
END
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users",
-- "value": [
-- {
-- "@odata.id": "https://graph.microsoft.com/v2/6d8ddd66-68d1-43b0-af5c-e31b4b7dd5cd/directoryObjects/fca490d8-5918-4201-8079-c5dcbeafcdc9/Microsoft.DirectoryServices.User",
-- "businessPhones": [
-- ],
-- "displayName": "Joe Sample",
-- "givenName": "Joe",
-- "jobTitle": null,
-- "mail": null,
-- "mobilePhone": null,
-- "officeLocation": null,
-- "preferredLanguage": null,
-- "surname": "Sample",
-- "userPrincipalName": "admin_chilkatsoft.com#EXT#@adminchilkatsoft.onmicrosoft.com",
-- "id": "fca490d8-5918-4201-8079-c5dcbeafcdc9"
-- }
-- ]
-- }
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @odata_id nvarchar(4000)
DECLARE @displayName nvarchar(4000)
DECLARE @givenName nvarchar(4000)
DECLARE @jobTitle nvarchar(4000)
DECLARE @mail nvarchar(4000)
DECLARE @mobilePhone nvarchar(4000)
DECLARE @officeLocation nvarchar(4000)
DECLARE @preferredLanguage nvarchar(4000)
DECLARE @surname nvarchar(4000)
DECLARE @userPrincipalName nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @odata_context nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @odata_context OUT, '"@odata.context"'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
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', @odata_id OUT, 'value[i]."@odata.id"'
EXEC sp_OAMethod @json, 'StringOf', @displayName OUT, 'value[i].displayName'
EXEC sp_OAMethod @json, 'StringOf', @givenName OUT, 'value[i].givenName'
EXEC sp_OAMethod @json, 'StringOf', @jobTitle OUT, 'value[i].jobTitle'
EXEC sp_OAMethod @json, 'StringOf', @mail OUT, 'value[i].mail'
EXEC sp_OAMethod @json, 'StringOf', @mobilePhone OUT, 'value[i].mobilePhone'
EXEC sp_OAMethod @json, 'StringOf', @officeLocation OUT, 'value[i].officeLocation'
EXEC sp_OAMethod @json, 'StringOf', @preferredLanguage OUT, 'value[i].preferredLanguage'
EXEC sp_OAMethod @json, 'StringOf', @surname OUT, 'value[i].surname'
EXEC sp_OAMethod @json, 'StringOf', @userPrincipalName OUT, 'value[i].userPrincipalName'
EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'value[i].id'
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].businessPhones'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbResponse
END
GO