(SQL Server) SharePoint Get Site ID

See more SharePoint Examples

Note: This example requires Chilkat v11.5.0 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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