SQL Server
SQL Server
Microsoft Teams - Create Team (complex request)
See more Microsoft Teams Examples
Create a team with multiple channels, installed apps, and pinned tabs using delegated permissionsChilkat 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 @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Build the following HTTP request:
-- POST https://graph.microsoft.com/v1.0/teams
-- Content-Type: application/json
--
-- {
-- "template@odata.bind": "https://graph.microsoft.com/v1.0/teamsTemplates('standard')",
-- "visibility": "Private",
-- "displayName": "Sample Engineering Team",
-- "description": "This is a sample engineering team, used to showcase the range of properties supported by this API",
-- "channels": [
-- {
-- "displayName": "Announcements",
-- "isFavoriteByDefault": true,
-- "description": "This is a sample announcements channel that is favorited by default. Use this channel to make important team, product, and service announcements."
-- },
-- {
-- "displayName": "Training",
-- "isFavoriteByDefault": true,
-- "description": "This is a sample training channel, that is favorited by default, and contains an example of pinned website and YouTube tabs.",
-- "tabs": [
-- {
-- "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.web')",
-- "name": "A Pinned Website",
-- "configuration": {
-- "contentUrl": "https://docs.microsoft.com/microsoftteams/microsoft-teams"
-- }
-- },
-- {
-- "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.youtube')",
-- "name": "A Pinned YouTube Video",
-- "configuration": {
-- "contentUrl": "https://tabs.teams.microsoft.com/Youtube/Home/YoutubeTab?videoId=X8krAMdGvCQ",
-- "websiteUrl": "https://www.youtube.com/watch?v=X8krAMdGvCQ"
-- }
-- }
-- ]
-- },
-- {
-- "displayName": "Planning",
-- "description": "This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.",
-- "isFavoriteByDefault": false
-- },
-- {
-- "displayName": "Issues and Feedback",
-- "description": "This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu."
-- }
-- ],
-- "memberSettings": {
-- "allowCreateUpdateChannels": true,
-- "allowDeleteChannels": true,
-- "allowAddRemoveApps": true,
-- "allowCreateUpdateRemoveTabs": true,
-- "allowCreateUpdateRemoveConnectors": true
-- },
-- "guestSettings": {
-- "allowCreateUpdateChannels": false,
-- "allowDeleteChannels": false
-- },
-- "funSettings": {
-- "allowGiphy": true,
-- "giphyContentRating": "Moderate",
-- "allowStickersAndMemes": true,
-- "allowCustomMemes": true
-- },
-- "messagingSettings": {
-- "allowUserEditMessages": true,
-- "allowUserDeleteMessages": true,
-- "allowOwnerDeleteMessages": true,
-- "allowTeamMentions": true,
-- "allowChannelMentions": true
-- },
-- "discoverySettings": {
-- "showInTeamsSearchAndSuggestions": true
-- },
-- "installedApps": [
-- {
-- "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.vsts')"
-- },
-- {
-- "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('1542629c-01b3-4a6d-8f76-1938b779e48d')"
-- }
-- ]
-- }
-- Use this online tool to generate code from sample JSON:
-- Generate Code to Create JSON
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, '"template@odata.bind"', 'https://graph.microsoft.com/v1.0/teamsTemplates(''standard'')'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'visibility', 'Private'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'displayName', 'Sample Engineering Team'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'description', 'This is a sample engineering team, used to showcase the range of properties supported by this API'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[0].displayName', 'Announcements'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[0].isFavoriteByDefault', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[0].description', 'This is a sample announcements channel that is favorited by default. Use this channel to make important team, product, and service announcements.'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].displayName', 'Training'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[1].isFavoriteByDefault', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].description', 'This is a sample training channel, that is favorited by default, and contains an example of pinned website and YouTube tabs.'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.web'')'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0].name', 'A Pinned Website'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0].configuration.contentUrl', 'https://docs.microsoft.com/microsoftteams/microsoft-teams'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.youtube'')'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].name', 'A Pinned YouTube Video'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].configuration.contentUrl', 'https://tabs.teams.microsoft.com/Youtube/Home/YoutubeTab?videoId=X8krAMdGvCQ'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].configuration.websiteUrl', 'https://www.youtube.com/watch?v=X8krAMdGvCQ'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[2].displayName', 'Planning'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[2].description', 'This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[2].isFavoriteByDefault', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[3].displayName', 'Issues and Feedback'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[3].description', 'This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateChannels', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowDeleteChannels', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowAddRemoveApps', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateRemoveTabs', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateRemoveConnectors', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'guestSettings.allowCreateUpdateChannels', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'guestSettings.allowDeleteChannels', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowGiphy', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'funSettings.giphyContentRating', 'Moderate'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowStickersAndMemes', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowCustomMemes', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowUserEditMessages', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowUserDeleteMessages', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowOwnerDeleteMessages', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowTeamMentions', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowChannelMentions', 1
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'discoverySettings.showInTeamsSearchAndSuggestions', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'installedApps[0]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.vsts'')'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'installedApps[1]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''1542629c-01b3-4a6d-8f76-1938b779e48d'')'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-type', 'application/json'
-- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://graph.microsoft.com/v1.0/teams', @json, 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- A successful response is indicated by a 202 response status code and an empty response body.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
PRINT 'Response Body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
END
GO