June 4, 2026

What the demo established

The demo called the HubSpot companies endpoint with a GET request. The first version used the HubSpot API as a live external system, then captured the result in an nvarchar(max) output variable.

DECLARE @response nvarchar(max);
DECLARE @return_code int;

EXECUTE @return_code = sp_invoke_external_rest_endpoint
  @url        = 'https://api.hubapi.com/crm/objects/2026-03/companies'
, @method     = 'GET'
, @credential = [https://api.hubapi.com]
, @response   = @response OUTPUT;

This is the core SQL Server 2025 shift. SQL Server is no longer only the place that receives the results of an application integration. In this pattern, SQL Server can initiate the external call itself.

The response is returned through @response. SQL Server wraps the HTTP response in a predictable structure. The important distinction is that the SQL Server wrapper and the vendor payload are not the same thing.

Conceptually, the response looks like this:

{
  "response": {
    "status": {
      "http": {
        "code": "200",
        "description": "OK"
      }
    },
    "headers": {}
  },
  "result": {}
}

The response object contains HTTP status and headers. The result object contains the payload returned by the API. In this demo, result contained the HubSpot company records.

Why it matters

Before this feature, many teams would use a separate application layer for this kind of work, even when the actual business logic lived in SQL. A .NET project might make the HTTP request, deserialize the JSON into classes, map the data into a table-valued parameter or staging table, call a stored procedure, and then let SQL do the real selection, joining, reconciliation, and reporting.

That model still has a place, especially for large applications, interactive workflows, complex OAuth flows, or user-facing systems. But it is often more machinery than a scheduled data sync really needs.

That is why the pre-read email, sent before the meeting, framed an API as “just a function.” A function receives inputs and returns an answer. A REST API does the same thing, except the function lives on another system and is reached over HTTPS.

Turning the response into a table

The HubSpot response contained an array under $.result.results. In SQL terms, that array is the rowset. Each item in the array is a company. Some fields sit at the top level, while business properties such as company name and domain sit under properties.

The demo used OPENJSON to turn that nested structure into rows and columns:

SELECT *
    FROM OPENJSON(@response, '$.result.results') WITH (
          id     nvarchar(100)
        , name   nvarchar(100) '$.properties.name'
        , domain nvarchar(100) '$.properties.domain'
    ) t;

That query is one of the most important examples on the page. It shows the practical bridge from API-shaped JSON back to SQL-shaped data.

Once the response is in rows and columns, the rest of the work becomes familiar. The result can be inserted into a staging table, joined to STAR data, used in a reconciliation report, compared to a prior snapshot, or fed into another stored procedure.

The response can also be inspected as JSON

In the meeting, the response was also cast as JSON so the structure could be inspected more easily inside SQL tooling:

SELECT CAST(@response AS json);

That was useful for teaching because it made the response feel more like XML inspection in older SQL workflows. The important point is not the visual inspection itself. The important point is that SQL Server can now hold the full API response, inspect its shape, and parse only the pieces the integration needs.

The underlying case

Most accounting-firm integrations are not just “get data from a vendor.” The real work is almost always the firm-specific interpretation of the data.

Those questions are usually easier to answer near the data. SQL Server is already where the firm can join STAR client, job, partner, office, status, billing, WIP, and custom-field information. If SQL can also make the API call, the integration can remain closer to the place where the rules are expressed.

Technical strengths of the pattern

The first strength is directness. A SQL developer can call the endpoint without creating a separate application project just to make the request.

The second strength is visibility. The stored procedure call, endpoint URL, JSON path, field extraction, and error handling can be read as SQL.

The third strength is composability. Once the API response becomes a rowset, the team can use ordinary SQL: joins, filters, inserts, updates, staging tables, logging tables, transactions, and reconciliation queries.

The fourth strength is reuse. The same pattern can be used for HubSpot, Microsoft Graph, DocuSign, SafeSend, CCH Axcess, ADP, Salesforce, GoFileRoom, SurePrep, DocuWare, or any other system with a compatible REST API and authentication pattern.

What the group challenged

The live demo intentionally started with the simple version of the API call. That made the concept easy to follow, but it also exposed the right production questions.

Kieren asked how failures should be handled when the API does not return a successful response.

The chat raised two additional concerns: how OAuth 2.0 token expiry and automatic refresh would work, and how to loop effectively over paginated endpoints.

The chat also correctly pointed out that a bearer token or input parameter should not be exposed in code or automation logs.

Those questions are exactly what move the feature from “cool demo” to “usable integration pattern.”

Return code handling

The stored procedure return value is important. A successful 2xx HTTP response returns 0. A completed HTTP call with a non-2xx response returns the HTTP status code. If SQL Server cannot make the HTTPS call at all, the procedure throws an exception.

That means production code should not assume the absence of a SQL exception means the vendor accepted the request.

The demo used this pattern:

IF @return_code <> 0 BEGIN;
    DECLARE @error_message nvarchar(4000) = JSON_QUERY(@response, '$.result');

    SET @error_message =
        'Error calling external REST endpoint: '
        + ISNULL(@error_message, 'Unknown error');

    THROW 50000, @error_message, 1;
END;

This is a good starting pattern because it pulls the vendor’s error payload out of $.result and turns it into a SQL error that can be seen by the caller, SQL Agent, a log table, or a monitoring process.

For production use, the procedure should usually log more than the final error message.

Protecting bearer tokens

The first version of an API demo often shows a bearer token inline because it makes the request easy to understand. That is not acceptable for production.

A token should not be scattered through stored procedures, source control, screenshots, job steps, error messages, or deployment scripts.

SQL Server’s better native pattern is a database-scoped credential. The credential stores the authentication data, and the stored procedure receives the credential name.

A simplified setup looks like this:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use-a-strong-password-here';
GO

CREATE DATABASE SCOPED CREDENTIAL [https://api.hubapi.com]
WITH
    IDENTITY = 'HTTPEndpointHeaders',
    SECRET = '{"Authorization":"Bearer <token-goes-here>"}';
GO

Then the API call can use the credential instead of a hard-coded Authorization header:

EXECUTE @return_code = sp_invoke_external_rest_endpoint 
      @url        = 'https://api.hubapi.com/crm/objects/2026-03/companies'
    , @method     = 'GET'
    , @credential = [https://api.hubapi.com]
    , @response   = @response OUTPUT;

Enabling the feature

One participant noted that the feature has to be enabled in SQL Server 2025. That is an important setup detail.

For SQL Server 2025, the feature is disabled by default and must be enabled at the server level:

EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

That is different from Azure SQL Database and Fabric SQL database, where the feature is enabled by default.

After the feature is enabled, permissions should still be handled carefully. A user or role that calls external endpoints needs permission to execute external endpoint calls, and a user that relies on a database-scoped credential needs permission to use that credential.

A simplified permission pattern looks like this:

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [YourUserOrRole];

GRANT REFERENCES
ON DATABASE SCOPED CREDENTIAL::[https://api.hubapi.com]
TO [YourUserOrRole];

The main principle is least privilege. Calling external APIs means SQL Server can transfer data outside the firm’s database environment. That power should be granted intentionally.

Paging

The live demo focused on the first page of HubSpot companies. The paging version was included afterward because paging is required for real production reads.

DECLARE @response nvarchar(max);
DECLARE @return_code int;

DECLARE @url nvarchar(4000) =
    'https://api.hubapi.com/crm/objects/2026-03/companies';

WHILE @url IS NOT NULL BEGIN;

    EXECUTE @return_code = sp_invoke_external_rest_endpoint 
          @url        = @url
        , @method     = 'GET'
        , @credential = [https://api.hubapi.com]
        , @response   = @response OUTPUT;

    IF @return_code <> 0 BEGIN;
        DECLARE @error_message nvarchar(4000) = JSON_QUERY(@response, '$.result');

        SET @error_message =
            'Error calling external REST endpoint: '
            + ISNULL(@error_message, 'Unknown error');

        THROW 50000, @error_message, 1;
    END;

    -- INSERT INTO dbo.hubspot_companies (id, name, domain)
    SELECT *
        FROM OPENJSON(@response, '$.result.results') WITH (
              id     nvarchar(100)
            , name   nvarchar(100) '$.properties.name'
            , domain nvarchar(100) '$.properties.domain'
        ) t;

    SET @url = JSON_VALUE(@response, '$.result.paging.next.link');

END;

This is one of the best code examples for the page because it is both small and realistic. It shows the recurring structure behind many API reads.

OAuth 2.0 and token refresh

The Teams chat raised the OAuth question: how does this handle OAuth 2.0 token expiry and automatic refresh?

The honest answer is that the June demo did not fully cover OAuth refresh. The demonstrated pattern works cleanly when the vendor provides a private app token, service token, API key, or other machine-to-machine credential that can be stored in a database-scoped credential.

OAuth can still be handled, but it introduces another layer of design.

For simple machine-to-machine integrations, this may still be reasonable in SQL. For interactive OAuth flows involving user consent, browser redirects, MFA, or multi-tenant authorization, a small helper service may still be the better tool.

Webhooks

Kieren also asked about webhooks. That is an important boundary.

sp_invoke_external_rest_endpoint lets SQL Server call an external endpoint. A webhook is usually the reverse: an external system calls an endpoint owned by the firm when something changes.

SQL Server can help with webhook setup if the vendor exposes an API for registering or managing webhook subscriptions. In that case, SQL can call the vendor’s registration endpoint.

But SQL Server is not itself a public HTTP listener for inbound webhook calls. If the firm wants to receive real-time webhook events, it usually still needs something like an Azure Function, API endpoint, service, or web application to receive the inbound call and then write the event into SQL.

Technical strengths of the production pattern

The production pattern is strong because it brings security, error handling, and paging into the same stored-procedure model as the API call itself.

What the second demo established

The second part of the demo showed the reverse direction: take STAR client data, shape it as JSON, and send it to HubSpot.

This is where the feature becomes more than a read-only reporting convenience. SQL Server can now participate in outbound system synchronization.

The demo used a view named star.companies to represent the STAR client data in a clean shape. In a real firm environment, that view would sit over STAR tables and expose the fields needed for synchronization.

CREATE view [star].[companies] as 
    select 
              star_client_number                = t.ClientRef
            , name                              = isnull(t.SearchName, '')
            , address                           = isnull(t.AddressDetail, '')
            , city                              = isnull(t.City, '')
            , state                             = isnull(s.Description, '')
            , phone                             = isnull(t.Telephone, '')
            , star_client_partner_name          = trim(concat(p_s.Forename, ' ', p_s.Surname))
            , star_client_entity_type           = isnull(ct.ClientType, '')
            , star_client_office                = isnull(o.OfficeName, '')
            , star_client_status                = isnull(cs.ClientStatus, '')
            , star_client_active_date           = isnull(cast(t.CreateDate as date), '1970-01-01')
            , star_client_inactive_date         = cast(t.CeaseDate as date)
            , star_client_total_credit_limit    = isnull(cast(t.TotalCreditLimit as decimal(19,2)), 0)
            , star_client_billings_ytd          = isnull(cast(xd.billingsYTD as decimal(19,2)), 0)
        from dbo.tblClient t
            left outer join dbo.tblClient pc on t.ClientID = pc.ClientID
            left outer join dbo.tblClientExtraDetails xd on t.ClientID = xd.ClientID
            left outer join dbo.tblPartner p on t.PartnerID = p.PartnerID
                left outer join dbo.tblStaff p_s on p.StaffID = p_s.StaffID
            left outer join dbo.tblClientType ct on t.ClientTypeID = ct.ClientTypeID
            left outer join dbo.tblOffice o on t.OfficeID = o.OfficeID
            left outer join dbo.tblClientStatus cs on t.ClientStatusID = cs.ClientStatusID
            left outer join dbo.tblState s on t.StateID = s.StateID;

This view is not just demo setup. It is the recommended architecture. The API procedure should not be responsible for knowing every STAR table and join. A clean STAR-facing view can expose the business shape, and the API-specific code can focus on the vendor payload.

Why the STAR view matters

The star.companies view creates a boundary between two concerns.

The first concern is STAR extraction. That includes joining STAR client tables, resolving lookup values, converting nulls, casting data types, and exposing consistent column names.

The second concern is HubSpot payload creation. That includes creating the JSON object expected by HubSpot, choosing the unique key, and deciding which properties to send.

Keeping those concerns separate makes the integration easier to understand and maintain.

The correlation key

HubSpot does not naturally know the STAR client number. The demo created a HubSpot custom property named star_client_number and treated it as a unique property.

That property became the correlation key between systems.

That is the foundation of upsert.

Why upsert matters

Without upsert, a synchronization process usually has to decide whether each record already exists.

That is workable, but it creates more moving parts.

The HubSpot batch upsert endpoint simplifies the write path. The caller sends the key and properties. HubSpot uses the unique property to decide whether to create or update the company.

That does not eliminate the need for logging or reconciliation, but it removes a large amount of insert-versus-update plumbing.

The target payload shape

The payload needs to be shaped around an inputs array. Each item contains an id, a properties object, and an idProperty value.

Conceptually:

{
  "inputs": [
    {
      "id": "12345",
      "properties": {
        "star_client_number": "12345",
        "name": "Sample Client",
        "phone": "555-123-4567",
        "city": "Chicago"
      },
      "idProperty": "star_client_number"
    }
  ]
}

Three ways to create the JSON in SQL

The demo showed three approaches. All of them work. The choice is mostly about readability and maintainability.

DECLARE @array nvarchar(max) = (
    SELECT 
          id                              = t.star_client_number
        , [properties.star_client_number] = t.star_client_number
        , [properties.phone]              = t.phone
        , [properties.city]               = t.city
        , idProperty                      = 'star_client_number'
    FROM star.companies t
    FOR JSON PATH, INCLUDE_NULL_VALUES
);

SELECT CAST(@array AS json) AS array;

DECLARE @array nvarchar(max) = (
    SELECT 
          id = t.star_client_number
        , properties = JSON_OBJECT(
              'star_client_number' : t.star_client_number,
              'name'               : t.name,
              'phone'              : t.phone,
              'city'               : t.city
              NULL ON NULL
          )
        , idProperty = 'star_client_number'
    FROM star.companies t
    FOR JSON PATH
);

SELECT CAST(@array AS json) AS array;

SET NOCOUNT ON;

DECLARE @array nvarchar(max) = (
    SELECT 
          id = t.star_client_number
        , properties = JSON_QUERY((
            SELECT t.star_client_number, t.name, t.phone, t.city
            FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
        ))
        , idProperty = 'star_client_number'
    FROM star.companies t
    FOR JSON PATH
);

SELECT CAST(@array AS json) AS array;

Wrapping the body

After creating the array, the payload must be wrapped in the top-level inputs property.

DECLARE @body nvarchar(max) =
    JSON_OBJECT('inputs' : JSON_QUERY(@array));

SELECT @body;

At that point, @body is the request payload.

A more elegant approach would be add the root on the FOR JSON PATH as shown here:

FOR JSON PATH, ROOT('inputs')

Calling the upsert endpoint

The upsert call uses the same stored procedure, but this time the method is POST and the JSON body is passed through @payload.

DECLARE @response nvarchar(max) = NULL;
DECLARE @return_code int = NULL;

DECLARE @url nvarchar(max) =
    'https://api.hubapi.com/crm/objects/2026-03/companies/batch/upsert';

EXECUTE @return_code = sp_invoke_external_rest_endpoint 
      @url        = @url
    , @credential = [https://api.hubapi.com]
    , @method     = 'POST'
    , @payload    = @body
    , @response   = @response OUTPUT;

SELECT
      @response AS response
    , @return_code AS return_code;

IF @return_code <> 0 BEGIN;
    DECLARE @api_error nvarchar(max) = CONCAT(
          'ERROR: '
        , @return_code
        , ' => '
        , JSON_QUERY(@response, '$.result')
    );

    THROW 50000, @api_error, 0;
END;

What happened in the demo

The HubSpot instance initially had only a couple of company records. The demo created a small set of fake STAR companies, shaped them through SQL, and posted them to HubSpot. After the upsert ran, those companies appeared in HubSpot.

The exact record count was not the point. The point was that SQL Server performed the outbound API call directly from T-SQL and did it with a small amount of code.

The underlying case

STAR-to-HubSpot synchronization is a good example because it is easy to understand:

In a real project, the same pattern could extend beyond companies.

The meeting mentioned that this type of synchronization had previously been done with stored procedures and scheduled execution. SQL Server 2025 makes the HTTP portion more native and cleaner.

Technical strengths of the upsert pattern

The first strength is simplification. Upsert removes much of the insert-versus-update decision logic.

The second strength is maintainability. The payload shape is visible in SQL.

The third strength is source control. The STAR view acts as the business source for what is allowed to leave STAR.

The fourth strength is reconciliation. Because SQL sent the request and captured the response, SQL can also log what was attempted and what came back.

The fifth strength is portability. The pattern is not limited to HubSpot. Any vendor with a compatible REST API can use the same basic shape: build body, call endpoint, parse response, log outcome.

The business conversation

The meeting ended with a broader conversation about where this fits inside real firms.

Debbie raised the most important management point: the integration still has to produce the business result the firm expects. Small code is not enough by itself. The process still has to be validated by management, partners, and the people who rely on the result.

That caution belongs on the page because it keeps the message grounded.

This feature reduces technical plumbing. It does not remove business analysis.

The advantage of the SQL-centered pattern is that those decisions can be reflected close to the code that executes them.

The code can become the requirement document

One of the strongest points from the discussion was that small, readable SQL can double as a requirements artifact.

In a large external integration project, the real requirements may be scattered across emails, vendor notes, mapping spreadsheets, compiled code, configuration screens, and tribal knowledge.

That does not make the work trivial, but it makes the work easier to inspect.

Internal teams versus vendors

Kent noted that STAR has supported several HubSpot integrations, often with third-party HubSpot specialists doing the integration work while STAR helps with the database side.

Brad spoke about an upcoming HubSpot implementation using a third-party.

Both comments are useful because they show that there is not one universal integration architecture.

The June demo is not an argument that every firm should abandon those models. It is an argument that SQL Server now deserves a more serious role in the design discussion.

Where SQL is a strong fit

SQL is a strong fit when the integration is:

These are exactly the kinds of processes many accounting firms already manage around STAR.

Where another layer may still be better

An application layer may still be better when the work is:

The practical message is not “SQL replaces the app team.” The practical message is “do not create an app layer just to do what SQL Server can now do directly.”

SQL Server Express and adoption

Another practical point from the meeting was that firms do not necessarily need the STAR production SQL Server itself to be on SQL Server 2025 before experimenting with this pattern.

A separate SQL Server 2025 instance could call the external API and read STAR through a linked server or staged extract, depending on the firm’s environment and policies.

SQL Server Express may be enough for small integration workloads, especially because API sync work is often not CPU-heavy. The external system performs much of the API-side work; SQL Server selects, shapes, sends, receives, parses, and logs.

Systems this pattern can apply to

HubSpot was the example, but the same general pattern can apply to many systems surrounding STAR and accounting-firm operations:

Every vendor has its own authentication model, rate limits, paging style, endpoint rules, object model, and licensing terms. But the pattern remains the same.