SQL Server
SQL Server
MS Graph Calendar List Events
See more Microsoft Calendar Examples
Retrieve a list of events in a calendar. The list contains single instance meetings and series masters.For more details, see https://docs.microsoft.com/en-us/graph/api/calendar-list-events?view=graph-rest-1.0
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 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
-- Use your previously obtained access token as shown here:
-- Get Microsoft Graph OAuth2 Access Token with Calendars.ReadWrite scope.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/msGraphCalendar.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Specify the calendar id
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', 'AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEGAAAA5_vF7TKKdE6bGCRqXyl2PQAClEpRTgAAAA=='
-- Send a GET request to https://graph.microsoft.com/v1.0/me/calendars/{$id}/events
DECLARE @strResponse nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @strResponse OUT, 'https://graph.microsoft.com/v1.0/me/calendars/{$id}/events'
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @strResponse
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Failed, response status code = ' + @iTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output:
-- (See parsing code below..)
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('admin%40chilkat.io')/calendars('AQMkADAwATM0MDAAMS1i ... AClEpRTgAAAA%3D%3D')/events",
-- "value": [
-- {
-- "@odata.etag": "W/\"5+vF7TKKdE6bGCRqXyl2PQAClIgmmw==\"",
-- "id": "AQMkADAwATM0MDAAM ... AApRZ7JkAAAA=",
-- "createdDateTime": "2019-04-26T14:31:39.8791929Z",
-- "lastModifiedDateTime": "2019-04-26T14:31:41.2753537Z",
-- "changeKey": "5+vF7TKKdE6bGCRqXyl2PQAClIgmmw==",
-- "categories": [
-- ],
-- "originalStartTimeZone": "Pacific Standard Time",
-- "originalEndTimeZone": "Pacific Standard Time",
-- "iCalUId": "040000008200E ... A230FEBFE5F7486A",
-- "reminderMinutesBeforeStart": 15,
-- "isReminderOn": true,
-- "hasAttachments": false,
-- "subject": "Let's go for lunch",
-- "bodyPreview": "Does mid month work for you?",
-- "importance": "normal",
-- "sensitivity": "normal",
-- "isAllDay": false,
-- "isCancelled": false,
-- "isOrganizer": true,
-- "responseRequested": true,
-- "seriesMasterId": null,
-- "showAs": "busy",
-- "type": "singleInstance",
-- "webLink": "https://outlook.live.com/owa/?itemid=AQMkADAwATM0MDAAMS1iNTcwLWI2NT ... gkal8pdj0AApRZ7JkAAAA%3D&exvsurl=1&path=/calendar/item",
-- "onlineMeetingUrl": null,
-- "recurrence": null,
-- "responseStatus": {
-- "response": "organizer",
-- "time": "0001-01-01T00:00:00Z"
-- },
-- "body": {
-- "contentType": "html",
-- "content": "<html>\r\n<head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\r\n<meta content=\"text/html; charset=us-ascii\">\r\n</head>\r\n<body>\r\nDoes mid month work for you?\r\n</body>\r\n</html>\r\n"
-- },
-- "start": {
-- "dateTime": "2019-11-15T20:00:00.0000000",
-- "timeZone": "UTC"
-- },
-- "end": {
-- "dateTime": "2019-11-15T22:00:00.0000000",
-- "timeZone": "UTC"
-- },
-- "location": {
-- "displayName": "Harry's Bar",
-- "locationType": "default",
-- "uniqueId": "Harry's Bar",
-- "uniqueIdType": "private"
-- },
-- "locations": [
-- {
-- "displayName": "Harry's Bar",
-- "locationType": "default",
-- "uniqueId": "Harry's Bar",
-- "uniqueIdType": "private"
-- }
-- ],
-- "attendees": [
-- {
-- "type": "required",
-- "status": {
-- "response": "none",
-- "time": "0001-01-01T00:00:00Z"
-- },
-- "emailAddress": {
-- "name": "Adele Vance",
-- "address": "adelev@contoso.onmicrosoft.com"
-- }
-- }
-- ],
-- "organizer": {
-- "emailAddress": {
-- "name": "Matt",
-- "address": "outlook_3A33FCEB9B74CC15@outlook.com"
-- }
-- }
-- }
-- ]
-- }
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @odataContext nvarchar(4000)
DECLARE @i int
DECLARE @count_i int
DECLARE @odataEtag nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @createdDateTime nvarchar(4000)
DECLARE @lastModifiedDateTime nvarchar(4000)
DECLARE @changeKey nvarchar(4000)
DECLARE @originalStartTimeZone nvarchar(4000)
DECLARE @originalEndTimeZone nvarchar(4000)
DECLARE @iCalUId nvarchar(4000)
DECLARE @reminderMinutesBeforeStart int
DECLARE @isReminderOn int
DECLARE @hasAttachments int
DECLARE @subject nvarchar(4000)
DECLARE @bodyPreview nvarchar(4000)
DECLARE @importance nvarchar(4000)
DECLARE @sensitivity nvarchar(4000)
DECLARE @isAllDay int
DECLARE @isCancelled int
DECLARE @isOrganizer int
DECLARE @responseRequested int
DECLARE @seriesMasterId nvarchar(4000)
DECLARE @showAs nvarchar(4000)
DECLARE @v_type nvarchar(4000)
DECLARE @webLink nvarchar(4000)
DECLARE @onlineMeetingUrl nvarchar(4000)
DECLARE @recurrence nvarchar(4000)
DECLARE @responseStatusResponse nvarchar(4000)
DECLARE @responseStatusTime nvarchar(4000)
DECLARE @bodyContentType nvarchar(4000)
DECLARE @bodyContent nvarchar(4000)
DECLARE @startDateTime nvarchar(4000)
DECLARE @startTimeZone nvarchar(4000)
DECLARE @endDateTime nvarchar(4000)
DECLARE @endTimeZone nvarchar(4000)
DECLARE @locationDisplayName nvarchar(4000)
DECLARE @locationLocationType nvarchar(4000)
DECLARE @locationUniqueId nvarchar(4000)
DECLARE @locationUniqueIdType nvarchar(4000)
DECLARE @organizerEmailAddressName nvarchar(4000)
DECLARE @organizerEmailAddressAddress nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @displayName nvarchar(4000)
DECLARE @locationType nvarchar(4000)
DECLARE @uniqueId nvarchar(4000)
DECLARE @uniqueIdType nvarchar(4000)
DECLARE @statusResponse nvarchar(4000)
DECLARE @statusTime nvarchar(4000)
DECLARE @emailAddressName nvarchar(4000)
DECLARE @emailAddressAddress nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @odataContext OUT, '"@odata.context"'
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', @odataEtag OUT, 'value[i]."@odata.etag"'
EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'value[i].id'
EXEC sp_OAMethod @json, 'StringOf', @createdDateTime OUT, 'value[i].createdDateTime'
EXEC sp_OAMethod @json, 'StringOf', @lastModifiedDateTime OUT, 'value[i].lastModifiedDateTime'
EXEC sp_OAMethod @json, 'StringOf', @changeKey OUT, 'value[i].changeKey'
EXEC sp_OAMethod @json, 'StringOf', @originalStartTimeZone OUT, 'value[i].originalStartTimeZone'
EXEC sp_OAMethod @json, 'StringOf', @originalEndTimeZone OUT, 'value[i].originalEndTimeZone'
EXEC sp_OAMethod @json, 'StringOf', @iCalUId OUT, 'value[i].iCalUId'
EXEC sp_OAMethod @json, 'IntOf', @reminderMinutesBeforeStart OUT, 'value[i].reminderMinutesBeforeStart'
EXEC sp_OAMethod @json, 'BoolOf', @isReminderOn OUT, 'value[i].isReminderOn'
EXEC sp_OAMethod @json, 'BoolOf', @hasAttachments OUT, 'value[i].hasAttachments'
EXEC sp_OAMethod @json, 'StringOf', @subject OUT, 'value[i].subject'
EXEC sp_OAMethod @json, 'StringOf', @bodyPreview OUT, 'value[i].bodyPreview'
EXEC sp_OAMethod @json, 'StringOf', @importance OUT, 'value[i].importance'
EXEC sp_OAMethod @json, 'StringOf', @sensitivity OUT, 'value[i].sensitivity'
EXEC sp_OAMethod @json, 'BoolOf', @isAllDay OUT, 'value[i].isAllDay'
EXEC sp_OAMethod @json, 'BoolOf', @isCancelled OUT, 'value[i].isCancelled'
EXEC sp_OAMethod @json, 'BoolOf', @isOrganizer OUT, 'value[i].isOrganizer'
EXEC sp_OAMethod @json, 'BoolOf', @responseRequested OUT, 'value[i].responseRequested'
EXEC sp_OAMethod @json, 'StringOf', @seriesMasterId OUT, 'value[i].seriesMasterId'
EXEC sp_OAMethod @json, 'StringOf', @showAs OUT, 'value[i].showAs'
EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'value[i].type'
EXEC sp_OAMethod @json, 'StringOf', @webLink OUT, 'value[i].webLink'
EXEC sp_OAMethod @json, 'StringOf', @onlineMeetingUrl OUT, 'value[i].onlineMeetingUrl'
EXEC sp_OAMethod @json, 'StringOf', @recurrence OUT, 'value[i].recurrence'
EXEC sp_OAMethod @json, 'StringOf', @responseStatusResponse OUT, 'value[i].responseStatus.response'
EXEC sp_OAMethod @json, 'StringOf', @responseStatusTime OUT, 'value[i].responseStatus.time'
EXEC sp_OAMethod @json, 'StringOf', @bodyContentType OUT, 'value[i].body.contentType'
EXEC sp_OAMethod @json, 'StringOf', @bodyContent OUT, 'value[i].body.content'
EXEC sp_OAMethod @json, 'StringOf', @startDateTime OUT, 'value[i].start.dateTime'
EXEC sp_OAMethod @json, 'StringOf', @startTimeZone OUT, 'value[i].start.timeZone'
EXEC sp_OAMethod @json, 'StringOf', @endDateTime OUT, 'value[i].end.dateTime'
EXEC sp_OAMethod @json, 'StringOf', @endTimeZone OUT, 'value[i].end.timeZone'
EXEC sp_OAMethod @json, 'StringOf', @locationDisplayName OUT, 'value[i].location.displayName'
EXEC sp_OAMethod @json, 'StringOf', @locationLocationType OUT, 'value[i].location.locationType'
EXEC sp_OAMethod @json, 'StringOf', @locationUniqueId OUT, 'value[i].location.uniqueId'
EXEC sp_OAMethod @json, 'StringOf', @locationUniqueIdType OUT, 'value[i].location.uniqueIdType'
EXEC sp_OAMethod @json, 'StringOf', @organizerEmailAddressName OUT, 'value[i].organizer.emailAddress.name'
EXEC sp_OAMethod @json, 'StringOf', @organizerEmailAddressAddress OUT, 'value[i].organizer.emailAddress.address'
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].categories'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
-- ...
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].locations'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'StringOf', @displayName OUT, 'value[i].locations[j].displayName'
EXEC sp_OAMethod @json, 'StringOf', @locationType OUT, 'value[i].locations[j].locationType'
EXEC sp_OAMethod @json, 'StringOf', @uniqueId OUT, 'value[i].locations[j].uniqueId'
EXEC sp_OAMethod @json, 'StringOf', @uniqueIdType OUT, 'value[i].locations[j].uniqueIdType'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].attendees'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'value[i].attendees[j].type'
EXEC sp_OAMethod @json, 'StringOf', @statusResponse OUT, 'value[i].attendees[j].status.response'
EXEC sp_OAMethod @json, 'StringOf', @statusTime OUT, 'value[i].attendees[j].status.time'
EXEC sp_OAMethod @json, 'StringOf', @emailAddressName OUT, 'value[i].attendees[j].emailAddress.name'
EXEC sp_OAMethod @json, 'StringOf', @emailAddressAddress OUT, 'value[i].attendees[j].emailAddress.address'
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
PRINT 'Success.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @json
END
GO