(SQL Server) DocuSign: Requesting a Signature via Email (Remote Signing)

See more DocuSign Examples

This code example demonstrates the simplest and quickest workflow for requesting a signature for a document via email. The email will contain a signing link the recipient can use to electronically sign a document from their mobile or desktop computer.

For more information, see https://developers.docusign.com/esign-rest-api/code-examples/quickstart-request-signature-email

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl --request POST https://demo.docusign.net/restapi/v2.1/accounts/${accountId}/envelopes \
    --      --header "Authorization: Bearer ${accessToken}" \
    --      --header "Content-Type: application/json" \
    --      --data '{
    --     "emailSubject": "Please sign this document",
    --     "documents": [
    --         {
    --             "documentBase64": "JVBERi0xLjMKMyAwIG9iag ... dGFydHhyZWYKNjk5CiUlRU9GCg==",
    --             "name": "Lorem Ipsum",
    --             "fileExtension": "pdf",
    --             "documentId": "1"
    --         }
    --     ],
    --     "recipients": {
    --         "signers": [
    --             {
    --                 "email": "joe_sample@example.com",
    --                 "name": "Joe Sample",
    --                 "recipientId": "1",
    --                 "routingOrder": "1",
    --                 "tabs": {
    --                     "signHereTabs": [
    --                         {
    --                             "documentId": "1", "pageNumber": "1",
    --                             "recipientId": "1", "tabLabel": "SignHereTab",
    --                             "xPosition": "195", "yPosition": "147"
    --                         }
    --                     ]
    --                 }
    --             }
    --         ]
    --     },
    --     "status": "sent"
    -- }'

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

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

    -- {
    --   "emailSubject": "Please sign this document",
    --   "documents": [
    --     {
    --       "documentBase64": "JVBERi0xLjMKMyAwIG9iag ... dGFydHhyZWYKNjk5CiUlRU9GCg==",
    --       "name": "Lorem Ipsum",
    --       "fileExtension": "pdf",
    --       "documentId": "1"
    --     }
    --   ],
    --   "recipients": {
    --     "signers": [
    --       {
    --         "email": "joe_sample@example.com",
    --         "name": "Joe Sample",
    --         "recipientId": "1",
    --         "routingOrder": "1",
    --         "tabs": {
    --           "signHereTabs": [
    --             {
    --               "documentId": "1",
    --               "pageNumber": "1",
    --               "recipientId": "1",
    --               "tabLabel": "SignHereTab",
    --               "xPosition": "195",
    --               "yPosition": "147"
    --             }
    --           ]
    --         }
    --       }
    --     ]
    --   },
    --   "status": "sent"
    -- }

    -- Load a PDF to be signed.
    DECLARE @pdfData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @pdfData OUT

    EXEC sp_OAMethod @pdfData, 'LoadFile', @success OUT, 'qa_data/pdf/helloWorld.pdf'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load local PDF file.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @pdfData
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'emailSubject', 'Please sign this document'
    EXEC sp_OAMethod @pdfData, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].documentBase64', @sTmp0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].name', 'Lorem Ipsum'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].fileExtension', 'pdf'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].documentId', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].email', 'joe_sample@example.com'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].name', 'Joe Sample'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].recipientId', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].routingOrder', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].documentId', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].pageNumber', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].recipientId', '1'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].tabLabel', 'SignHereTab'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].xPosition', '195'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'recipients.signers[0].tabs.signHereTabs[0].yPosition', '147'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'status', 'sent'

    -- Get our previously obtained OAuth2 access token, which should contain JSON like this:
    -- {
    --   "access_token": "eyJ0eXA....YQyig",
    --   "token_type": "Bearer",
    --   "refresh_token": "eyJ0eXA....auE3eHKg",
    --   "expires_in": 28800
    -- }

    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/docusign.json'

    DECLARE @sbAuth int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT

    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', @sTmp0
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'

    -- Don't forget to modify this line to use your account ID
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://demo.docusign.net/restapi/v2.1/accounts/${accountId}/envelopes', '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 @pdfData
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @sbAuth
        RETURN
      END

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
    DECLARE @jResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.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 @pdfData
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @sbAuth
        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)

    -- {
    --   "envelopeId": "d51cfdab-22ed-4832-bf68-446c44077ffc",
    --   "uri": "/envelopes/d51cfdab-22ed-4832-bf68-446c44077ffc",
    --   "statusDateTime": "2018-04-17T16:31:51.8830000Z",
    --   "status": "sent"
    -- }

    -- 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 @envelopeId nvarchar(4000)

    DECLARE @uri nvarchar(4000)

    DECLARE @statusDateTime nvarchar(4000)

    DECLARE @status nvarchar(4000)

    EXEC sp_OAMethod @jResp, 'StringOf', @envelopeId OUT, 'envelopeId'
    EXEC sp_OAMethod @jResp, 'StringOf', @uri OUT, 'uri'
    EXEC sp_OAMethod @jResp, 'StringOf', @statusDateTime OUT, 'statusDateTime'
    EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'status'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @pdfData
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @sbAuth
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO