(SQL Server) Yousign - Setup email notifications

Demonstrates how to setup email notifications based on events. An event is triggered when a user (or our API) make an action or when a status has changed.

For more information, see https://dev.yousign.com/?version=latest#96b9f5ec-8e41-4d0f-82e5-6fa91adfa4fd

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
    DECLARE @sTmp0 nvarchar(4000)
    -- 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_9_5_0.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl --location --request POST 'https://staging-api.yousign.com/procedures' \
    -- --header 'Authorization: Bearer YOUR_API_KEY' \
    -- --header 'Content-Type: application/json' \
    -- --data-raw '{
    --     "name": "My first procedure with emails",
    --     "description": "Powerful! Here is the description of my first procedure with emails",
    --     "members": [
    --         {
    --             "firstname": "John",
    --             "lastname": "Doe",
    --             "email": "john.doe@yousign.fr",
    --             "phone": "+33612345678",
    --             "fileObjects": [
    --                 {
    --                     "file": "/files/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --                     "page": 2,
    --                     "position": "230,499,464,589",
    --                     "mention": "Read and approved",
    --                     "mention2": "Signed by John Doe"
    --                 }
    --             ]
    --         }
    --     ],
    --     "config": {
    --         "email": {
    --             "member.started": [
    --                 {
    --                     "subject": "Hey! You are invited to sign!",
    --                     "message": "Hello <tag data-tag-type=\"string\" data-tag-name=\"recipient.firstname\"></tag> <tag data-tag-type=\"string\" data-tag-name=\"recipient.lastname\"></tag>, <br><br> You have ben invited to sign a document, please click on the following button to read it: <tag data-tag-type=\"button\" data-tag-name=\"url\" data-tag-title=\"Access to documents\">Access to documents</tag>",
    --                     "to": ["@member"]
    --                 }
    --             ],
    --             "procedure.started": [
    --                 {
    --                     "subject": "John, created a procedure your API have.",
    --                     "message": "The content of this email is totally awesome.",
    --                     "to": ["@creator", "@members", "billing@yousign.fr"]
    --                 }
    --             ]
    --         }
    --     }
    -- }'

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- Use this online tool to generate code from sample JSON:
    -- Generate Code to Create JSON

    -- The following JSON is sent in the request body.

    -- {
    --   "name": "My first procedure with emails",
    --   "description": "Powerful! Here is the description of my first procedure with emails",
    --   "members": [
    --     {
    --       "firstname": "John",
    --       "lastname": "Doe",
    --       "email": "john.doe@yousign.fr",
    --       "phone": "+33612345678",
    --       "fileObjects": [
    --         {
    --           "file": "/files/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --           "page": 2,
    --           "position": "230,499,464,589",
    --           "mention": "Read and approved",
    --           "mention2": "Signed by John Doe"
    --         }
    --       ]
    --     }
    --   ],
    --   "config": {
    --     "email": {
    --       "member.started": [
    --         {
    --           "subject": "Hey! You are invited to sign!",
    --           "message": "Hello <tag data-tag-type=",
    --           " data-tag-name=": <tag,
    --           "button": [
    --             "@member"
    --           ]
    --         }
    --       ],
    --       "procedure.started": [
    --         {
    --           "subject": "John, created a procedure your API have.",
    --           "message": "The content of this email is totally awesome.",
    --           "to": [
    --             "@creator",
    --             "@members",
    --             "billing@yousign.fr"
    --           ]
    --         }
    --       ]
    --     }
    --   }
    -- }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'name', 'My first procedure with emails'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'description', 'Powerful! Here is the description of my first procedure with emails'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].firstname', 'John'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].lastname', 'Doe'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].email', 'john.doe@yousign.fr'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].phone', '+33612345678'
    -- Use the actual file ID here...
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].fileObjects[0].file', '/files/23686fbe-3ae1-4de9-9e01-58bbf29b2b18'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'members[0].fileObjects[0].page', 2
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].fileObjects[0].position', '230,499,464,589'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].fileObjects[0].mention', 'Read and approved'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].fileObjects[0].mention2', 'Signed by John Doe'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."member.started"[0].subject', 'Hey! You are invited to sign!'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."member.started"[0].message', 'Hello <tag data-tag-type="string" data-tag-name="recipient.firstname"></tag> <tag data-tag-type="string" data-tag-name="recipient.lastname"></tag>, <br><br> You have ben invited to sign a document, please click on the following button to read it: <tag data-tag-type="button" data-tag-name="url" data-tag-title="Access to documents">Access to documents</tag>'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."member.started"[0].to[0]', '@member'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."procedure.started"[0].subject', 'John, created a procedure your API have.'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."procedure.started"[0].message', 'The content of this email is totally awesome.'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."procedure.started"[0].to[0]', '@creator'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."procedure.started"[0].to[1]', '@members'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'config.email."procedure.started"[0].to[2]', 'billing@yousign.fr'

    -- Adds the "Authorization: Bearer YOUR_API_KEY" header.
    EXEC sp_OASetProperty @http, 'AuthToken', 'YOUR_API_KEY'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://staging-api.yousign.com/procedures', 'application/json', @json
    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 @json
        RETURN
      END

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "id": "/procedures/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --   "name": "My first procedure with emails",
    --   "description": "Powerful! Here is the description of my first procedure with emails",
    --   "createdAt": "2018-12-05T06:53:34+01:00",
    --   "updatedAt": "2018-12-05T06:53:34+01:00",
    --   "finishedAt": null,
    --   "expiresAt": null,
    --   "status": "active",
    --   "creator": null,
    --   "creatorFirstName": null,
    --   "creatorLastName": null,
    --   "workspace": "/workspaces/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --   "template": false,
    --   "ordered": false,
    --   "parent": null,
    --   "metadata": [
    --   ],
    --   "config": {
    --     "email": {
    --       "procedure.started": [
    --         {
    --           "subject": "John, created a procedure your API have.",
    --           "message": "The content of this email is totally awesome.",
    --           "to": [
    --             "@creator",
    --             "@members",
    --             "billing@yousign.fr"
    --           ]
    --         }
    --       ],
    --       "member.started": [
    --         {
    --           "subject": "Hey! You are invited to sign!",
    --           "message": "Hello <tag data-tag-type=\"string\" data-tag-name=\"recipient.firstname\"></tag> <tag data-tag-type=\"string\" data-tag-name=\"recipient.lastname\"></tag>, <br><br> You have ben invited to sign a document, please click on the following button to read it: <tag data-tag-type=\"button\" data-tag-name=\"url\" data-tag-title=\"Access to documents\">Access to documents</tag>",
    --           "to": [
    --             "@member"
    --           ]
    --         }
    --       ]
    --     }
    --   },
    --   "members": [
    --     {
    --       "id": "/members/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --       "user": null,
    --       "type": "signer",
    --       "firstname": "John",
    --       "lastname": "Doe",
    --       "email": "john.doe@yousign.fr",
    --       "phone": "+33612345678",
    --       "position": 1,
    --       "createdAt": "2018-12-05T06:53:34+01:00",
    --       "updatedAt": "2018-12-05T06:53:34+01:00",
    --       "finishedAt": null,
    --       "status": "pending",
    --       "fileObjects": [
    --         {
    --           "id": "/file_objects/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --           "file": {
    --             "id": "/files/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --             "name": "The best name for my file.pdf",
    --             "type": "signable",
    --             "contentType": "application/pdf",
    --             "description": null,
    --             "createdAt": "2018-12-05T06:52:54+01:00",
    --             "updatedAt": "2018-12-05T06:53:34+01:00",
    --             "sha256": "bb57ae2b2ca6ad0133a699350d1a6f6c8cdfde3cf872cf526585d306e4675cc2",
    --             "metadata": [
    --             ],
    --             "workspace": "/workspaces/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --             "creator": null,
    --             "protected": false,
    --             "position": 0,
    --             "parent": null
    --           },
    --           "page": 2,
    --           "position": "230,499,464,589",
    --           "fieldName": null,
    --           "mention": "Read and approved",
    --           "mention2": "Signed by John Doe",
    --           "createdAt": "2018-12-05T06:53:34+01:00",
    --           "updatedAt": "2018-12-05T06:53:34+01:00",
    --           "parent": null,
    --           "reason": "Signed by Yousign"
    --         }
    --       ],
    --       "comment": null,
    --       "notificationsEmail": [
    --       ],
    --       "operationLevel": "custom",
    --       "operationCustomModes": [
    --         "sms"
    --       ],
    --       "operationModeSmsConfig": null,
    --       "parent": null
    --     }
    --   ],
    --   "subscribers": [
    --   ],
    --   "files": [
    --     {
    --       "id": "/files/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --       "name": "The best name for my file.pdf",
    --       "type": "signable",
    --       "contentType": "application/pdf",
    --       "description": null,
    --       "createdAt": "2018-12-05T06:52:54+01:00",
    --       "updatedAt": "2018-12-05T06:53:34+01:00",
    --       "sha256": "bb57ae2b2ca6ad0133a699350d1a6f6c8cdfde3cf872cf526585d306e4675cc2",
    --       "metadata": [
    --       ],
    --       "workspace": "/workspaces/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    --       "creator": null,
    --       "protected": false,
    --       "position": 0,
    --       "parent": null
    --     }
    --   ],
    --   "relatedFilesEnable": false,
    --   "archive": false,
    --   "archiveMetadata": [
    --   ],
    --   "fields": [
    --   ],
    --   "permissions": [
    --   ]
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @subject nvarchar(4000)

    DECLARE @message nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @strVal nvarchar(4000)

    DECLARE @user nvarchar(4000)

    DECLARE @v_type nvarchar(4000)

    DECLARE @firstname nvarchar(4000)

    DECLARE @lastname nvarchar(4000)

    DECLARE @email nvarchar(4000)

    DECLARE @phone nvarchar(4000)

    DECLARE @position int

    DECLARE @comment nvarchar(4000)

    DECLARE @operationLevel nvarchar(4000)

    DECLARE @operationModeSmsConfig nvarchar(4000)

    DECLARE @fileId nvarchar(4000)

    DECLARE @fileName nvarchar(4000)

    DECLARE @fileType nvarchar(4000)

    DECLARE @fileContentType nvarchar(4000)

    DECLARE @fileDescription nvarchar(4000)

    DECLARE @fileCreatedAt nvarchar(4000)

    DECLARE @fileUpdatedAt nvarchar(4000)

    DECLARE @fileSha256 nvarchar(4000)

    DECLARE @fileWorkspace nvarchar(4000)

    DECLARE @fileCreator nvarchar(4000)

    DECLARE @fileProtected int

    DECLARE @filePosition int

    DECLARE @fileParent nvarchar(4000)

    DECLARE @page int

    DECLARE @position_str nvarchar(4000)

    DECLARE @fieldName nvarchar(4000)

    DECLARE @mention nvarchar(4000)

    DECLARE @mention2 nvarchar(4000)

    DECLARE @reason nvarchar(4000)

    DECLARE @k int

    DECLARE @count_k int

    DECLARE @contentType nvarchar(4000)

    DECLARE @sha256 nvarchar(4000)

    DECLARE @v_protected int

    DECLARE @id nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'id'
    DECLARE @name nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'name'
    DECLARE @description nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'description'
    DECLARE @createdAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @createdAt OUT, 'createdAt'
    DECLARE @updatedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @updatedAt OUT, 'updatedAt'
    DECLARE @finishedAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @finishedAt OUT, 'finishedAt'
    DECLARE @expiresAt nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @expiresAt OUT, 'expiresAt'
    DECLARE @status nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'status'
    DECLARE @creator nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @creator OUT, 'creator'
    DECLARE @creatorFirstName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @creatorFirstName OUT, 'creatorFirstName'
    DECLARE @creatorLastName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @creatorLastName OUT, 'creatorLastName'
    DECLARE @workspace nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @workspace OUT, 'workspace'
    DECLARE @template int
    EXEC sp_OAMethod @jResp, 'BoolOf', @template OUT, 'template'
    DECLARE @ordered int
    EXEC sp_OAMethod @jResp, 'BoolOf', @ordered OUT, 'ordered'
    DECLARE @parent nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @parent OUT, 'parent'
    DECLARE @relatedFilesEnable int
    EXEC sp_OAMethod @jResp, 'BoolOf', @relatedFilesEnable OUT, 'relatedFilesEnable'
    DECLARE @archive int
    EXEC sp_OAMethod @jResp, 'BoolOf', @archive OUT, 'archive'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'metadata'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'config.email."procedure.started"'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @subject OUT, 'config.email."procedure.started"[i].subject'
        EXEC sp_OAMethod @jResp, 'StringOf', @message OUT, 'config.email."procedure.started"[i].message'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'config.email."procedure.started"[i].to'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'config.email."procedure.started"[i].to[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'config.email."member.started"'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @subject OUT, 'config.email."member.started"[i].subject'
        EXEC sp_OAMethod @jResp, 'StringOf', @message OUT, 'config.email."member.started"[i].message'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'config.email."member.started"[i].to'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'config.email."member.started"[i].to[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'members'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'members[i].id'
        EXEC sp_OAMethod @jResp, 'StringOf', @user OUT, 'members[i].user'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'members[i].type'
        EXEC sp_OAMethod @jResp, 'StringOf', @firstname OUT, 'members[i].firstname'
        EXEC sp_OAMethod @jResp, 'StringOf', @lastname OUT, 'members[i].lastname'
        EXEC sp_OAMethod @jResp, 'StringOf', @email OUT, 'members[i].email'
        EXEC sp_OAMethod @jResp, 'StringOf', @phone OUT, 'members[i].phone'
        EXEC sp_OAMethod @jResp, 'IntOf', @position OUT, 'members[i].position'
        EXEC sp_OAMethod @jResp, 'StringOf', @createdAt OUT, 'members[i].createdAt'
        EXEC sp_OAMethod @jResp, 'StringOf', @updatedAt OUT, 'members[i].updatedAt'
        EXEC sp_OAMethod @jResp, 'StringOf', @finishedAt OUT, 'members[i].finishedAt'
        EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'members[i].status'
        EXEC sp_OAMethod @jResp, 'StringOf', @comment OUT, 'members[i].comment'
        EXEC sp_OAMethod @jResp, 'StringOf', @operationLevel OUT, 'members[i].operationLevel'
        EXEC sp_OAMethod @jResp, 'StringOf', @operationModeSmsConfig OUT, 'members[i].operationModeSmsConfig'
        EXEC sp_OAMethod @jResp, 'StringOf', @parent OUT, 'members[i].parent'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'members[i].fileObjects'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'members[i].fileObjects[j].id'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileId OUT, 'members[i].fileObjects[j].file.id'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileName OUT, 'members[i].fileObjects[j].file.name'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileType OUT, 'members[i].fileObjects[j].file.type'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileContentType OUT, 'members[i].fileObjects[j].file.contentType'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileDescription OUT, 'members[i].fileObjects[j].file.description'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileCreatedAt OUT, 'members[i].fileObjects[j].file.createdAt'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileUpdatedAt OUT, 'members[i].fileObjects[j].file.updatedAt'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileSha256 OUT, 'members[i].fileObjects[j].file.sha256'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileWorkspace OUT, 'members[i].fileObjects[j].file.workspace'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileCreator OUT, 'members[i].fileObjects[j].file.creator'
            EXEC sp_OAMethod @jResp, 'BoolOf', @fileProtected OUT, 'members[i].fileObjects[j].file.protected'
            EXEC sp_OAMethod @jResp, 'IntOf', @filePosition OUT, 'members[i].fileObjects[j].file.position'
            EXEC sp_OAMethod @jResp, 'StringOf', @fileParent OUT, 'members[i].fileObjects[j].file.parent'
            EXEC sp_OAMethod @jResp, 'IntOf', @page OUT, 'members[i].fileObjects[j].page'
            EXEC sp_OAMethod @jResp, 'StringOf', @position_str OUT, 'members[i].fileObjects[j].position'
            EXEC sp_OAMethod @jResp, 'StringOf', @fieldName OUT, 'members[i].fileObjects[j].fieldName'
            EXEC sp_OAMethod @jResp, 'StringOf', @mention OUT, 'members[i].fileObjects[j].mention'
            EXEC sp_OAMethod @jResp, 'StringOf', @mention2 OUT, 'members[i].fileObjects[j].mention2'
            EXEC sp_OAMethod @jResp, 'StringOf', @createdAt OUT, 'members[i].fileObjects[j].createdAt'
            EXEC sp_OAMethod @jResp, 'StringOf', @updatedAt OUT, 'members[i].fileObjects[j].updatedAt'
            EXEC sp_OAMethod @jResp, 'StringOf', @parent OUT, 'members[i].fileObjects[j].parent'
            EXEC sp_OAMethod @jResp, 'StringOf', @reason OUT, 'members[i].fileObjects[j].reason'
            SELECT @k = 0
            EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_k OUT, 'members[i].fileObjects[j].file.metadata'
            WHILE @k < @count_k
              BEGIN
                EXEC sp_OASetProperty @jResp, 'K', @k
                SELECT @k = @k + 1
              END
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'members[i].notificationsEmail'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'members[i].operationCustomModes'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'members[i].operationCustomModes[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'subscribers'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'files'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'files[i].id'
        EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'files[i].name'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'files[i].type'
        EXEC sp_OAMethod @jResp, 'StringOf', @contentType OUT, 'files[i].contentType'
        EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'files[i].description'
        EXEC sp_OAMethod @jResp, 'StringOf', @createdAt OUT, 'files[i].createdAt'
        EXEC sp_OAMethod @jResp, 'StringOf', @updatedAt OUT, 'files[i].updatedAt'
        EXEC sp_OAMethod @jResp, 'StringOf', @sha256 OUT, 'files[i].sha256'
        EXEC sp_OAMethod @jResp, 'StringOf', @workspace OUT, 'files[i].workspace'
        EXEC sp_OAMethod @jResp, 'StringOf', @creator OUT, 'files[i].creator'
        EXEC sp_OAMethod @jResp, 'BoolOf', @v_protected OUT, 'files[i].protected'
        EXEC sp_OAMethod @jResp, 'IntOf', @position OUT, 'files[i].position'
        EXEC sp_OAMethod @jResp, 'StringOf', @parent OUT, 'files[i].parent'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'files[i].metadata'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'archiveMetadata'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'permissions'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO