SQL Server
SQL Server
SharePoint Get Site ID
See more SharePoint Examples
This example demonstrates how to use the Chilkat HttpCurl class together with Microsoft Graph and OAuth2 Client Credentials authentication to retrieve the ID of a SharePoint site. The example automatically obtains an access token, sends a request to Microsoft Graph to locate a site by hostname and site name, and extracts the site's ID, description, and hostname from the JSON response. The retrieved site ID can then be used in subsequent SharePoint operations such as accessing document libraries, browsing folders, and managing files.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 retrieves the Microsoft Graph site ID for a SharePoint site.
--
-- The site ID is required for many subsequent Microsoft Graph operations,
-- such as enumerating document libraries, listing files, downloading content,
-- or creating folders within a SharePoint site.
SELECT @success = 0
-- --------------------------------------------------------------------------------------------------------
-- Before running this example, create an Azure App Registration and grant it
-- the Microsoft Graph permissions required to access SharePoint.
--
-- The application will authenticate using OAuth2 Client Credentials.
-- See:
-- How to Create SharePoint App Registration for OAuth 2.0 Client Credentials
-- --------------------------------------------------------------------------------------------------------
-- Build a JSON authentication configuration.
-- HttpCurl will use this information to automatically obtain OAuth2 access tokens.
DECLARE @jsonAuth int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonAuth OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Enable secret lookup.
--
-- Instead of hard-coding sensitive values such as the client ID,
-- client secret, and token endpoint, secret specification strings
-- are used. Chilkat automatically retrieves the actual values from
-- Windows Credential Manager (Windows) or Apple Keychain (macOS).
--
-- See:
-- Secret Specification Strings
EXEC sp_OASetProperty @jsonAuth, 'EnableSecrets', 1
EXEC sp_OAMethod @jsonAuth, 'UpdateString', @success OUT, 'oauth2.client_id', '!!sharepoint|oauth2|client_id'
IF @success = 1
BEGIN
EXEC sp_OAMethod @jsonAuth, 'UpdateString', @success OUT, 'oauth2.client_secret', '!!sharepoint|oauth2|client_secret'
END
IF @success = 1
BEGIN
EXEC sp_OAMethod @jsonAuth, 'UpdateString', @success OUT, 'oauth2.token_endpoint', '!!sharepoint|oauth2|token_endpoint'
END
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonAuth, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonAuth
RETURN
END
-- Request Microsoft Graph permissions that were granted to the application.
EXEC sp_OAMethod @jsonAuth, 'UpdateString', @success OUT, 'oauth2.scope', 'https://graph.microsoft.com/.default'
-- ---------------------------------------------------------------------------------------------------
DECLARE @curl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @curl OUT
-- Associate the OAuth2 configuration with HttpCurl.
--
-- When the request is executed, Chilkat automatically obtains an access token
-- (if needed) and adds the Authorization: Bearer header to the HTTP request.
EXEC sp_OAMethod @curl, 'SetAuth', @success OUT, @jsonAuth
-- Define variables whose values are already known.
--
-- These variables are referenced in the curl command using
-- {{variable_name}} substitution syntax.
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'sharepoint_hostname', 'example.sharepoint.com'
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'site_name', 'test'
-- Define the values we want to extract from the JSON response.
--
-- The first argument is a JSON path within the response.
-- The second argument is the name of the HttpCurl variable that
-- will receive the extracted value.
EXEC sp_OAMethod @curl, 'AddTargetOutput', NULL, 'id', 'site_id'
EXEC sp_OAMethod @curl, 'AddTargetOutput', NULL, 'description', 'site_description'
EXEC sp_OAMethod @curl, 'AddTargetOutput', NULL, 'siteCollection.hostname', 'site_hostname'
-- Microsoft Graph request:
--
-- GET /sites/{hostname}:/sites/{site-name}
--
-- This returns information about the SharePoint site, including
-- the site ID that is needed for many later SharePoint operations.
--
-- No Authorization header is included because HttpCurl automatically
-- adds it when OAuth2 authentication is configured.
DECLARE @curlCommand nvarchar(4000)
SELECT @curlCommand = 'curl -X GET "https://graph.microsoft.com/v1.0/sites/{{sharepoint_hostname}}:/sites/{{site_name}}"'
-- Execute the request.
EXEC sp_OAMethod @curl, 'DoYourThing', @success OUT, @curlCommand
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @curl
RETURN
END
-- A successful Graph response should return HTTP 200.
-- Any other status code typically indicates an authentication,
-- permission, or resource lookup error.
DECLARE @statusCode int
EXEC sp_OAGetProperty @curl, 'StatusCode', @statusCode OUT
IF @statusCode <> 200
BEGIN
EXEC sp_OAGetProperty @curl, 'ResponseBodyStr', @sTmp0 OUT
PRINT @sTmp0
PRINT 'status code = ' + @statusCode
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @curl
RETURN
END
-- Verify that all target outputs were successfully extracted.
--
-- The special variable name "!" tells VarDefined to return 1
-- only if every target output defined by AddTargetOutput was found
-- in the response JSON.
EXEC sp_OAMethod @curl, 'VarDefined', @iTmp0 OUT, '!'
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @curl, 'ResponseBodyStr', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Not all target outputs were found...'
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @curl
RETURN
END
-- Display the extracted values.
EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'site_id'
PRINT 'site_id = ' + @sTmp0
EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'site_description'
PRINT 'site_description = ' + @sTmp0
EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'site_hostname'
PRINT 'site_hostname = ' + @sTmp0
PRINT 'Success.'
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @curl
END
GO