Microsoft SQL Server integration

What it does

The Microsoft SQL Server integration lets your agency read and write a client's SQL Server database directly from a workflow. Actions find, get, query, insert, update, upsert, and delete rows, and run any single statement, such as a stored procedure. Triggers start a workflow when data changes. On SQL Server 2025, changes arrive instantly from a small forwarding trigger you add to the table. On any version, TaskJuice can instead check on an interval for new rows, new or changed rows, new results from your own query, new tables, or new columns.

Connection details are encrypted at rest, every value you pass is bound as a parameter rather than spliced into the SQL, and results are capped so a large read cannot overwhelm a run.

Connect a SQL Server database

1. Open your workspace in TaskJuice and add a SQL Server connection on the node that uses it.
2. Paste a connection string, for example sqlserver://user:password@db.example.com:1433/appdb.
3. Or fill in the host, port (1433 by default), user, password, and database separately. Connections are encrypted and validate the certificate by default.
4. Save the connection. The password and connection string are stored encrypted and are never shown again or written to run output.

Provision a least-privilege SQL login scoped to the tables the workflow touches, and expose the database on a public host the workflow can reach over TLS. Private and internal addresses are blocked. A login with only db_datareader is enough for the read actions and the interval triggers.

Network access. TaskJuice reaches the database from more than one network. Workflow steps in a published run connect from a fixed set of outbound addresses. The interval triggers and the editor's Test step connect from cloud infrastructure that has no fixed IP addresses. If the database only accepts listed addresses, those connections time out, so a step that fails its editor test with a connection error can still work in a published run. Unless you have a reason to restrict it, allow access from anywhere (0.0.0.0/0) and let a long, random password and TLS protect the database.

Triggers

• Row Inserted (Instant), Row Updated (Instant), and Row Deleted (Instant) fire the moment a statement inserts, updates, or deletes rows in a table. They need SQL Server 2025 and no polling. Setup is below.
• New Row fires when a row is added to a table. Point it at a column whose value only grows for new rows, usually the IDENTITY primary key.
• New or Updated Row fires when a row is added or changed. Point it at a datetime2, datetime, datetimeoffset, or date column that is set on insert and on every update, with a unique ID column as the tie-breaker. SQL Server has no ON UPDATE clause, so set the column from your application or from an UPDATE trigger. A rowversion column does not work here.
• New Row (Custom Query) fires when your own SELECT returns a row it has not returned before. Name a unique column so a row fires once even if its other values change later.
• New or Updated Row (Custom Query) fires when your own SELECT returns a row that is new or has any value changed since the last check.
• New Table fires when a table or view is created in the login's default schema, usually dbo.
• New Column fires when a column is added to a table in the login's default schema. Name a table to watch just that one, or leave it empty to watch them all.

The interval triggers check on an interval you choose (every 5 minutes by default, as often as every minute). Each check runs one small query. Rows, tables, and columns that already exist when you turn a trigger on never fire; only changes after that point do. Every check that finds something starts one run whose items array holds everything found, so add a Loop node to handle the items one at a time.

Set up the instant triggers (SQL Server 2025)

SQL Server 2025 can call an HTTPS endpoint from T-SQL with sp_invoke_external_rest_endpoint. The script below adds a trigger to a table that sends each insert, update, or delete statement to TaskJuice, which starts every published workflow that uses the matching instant trigger. You set this up once per table; each workflow only picks the trigger it needs.

1. Add one of the instant triggers to a workflow and copy the SQL Server ingress URL it shows. The URL is private to your workspace, so treat it like a password.
2. Connect to the database as a member of db_owner (step 1 of the script needs sysadmin, or the ALTER SETTINGS permission, once per server).
3. Run the script below. Paste your ingress URL in place of <your SQL Server ingress URL>, and replace dbo.orders with your table in step 3 of the script.
4. For each further table, run step 3 again with that table's name.

-- 1. Once per server (needs sysadmin or ALTER SETTINGS).
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- 2. Once per database: where to send changes, and the procedure that sends them.
CREATE SCHEMA automation_meta;
GO
CREATE TABLE automation_meta.forward_target (
  id INT NOT NULL CONSTRAINT pk_forward_target PRIMARY KEY CHECK (id = 1),
  url NVARCHAR(4000) NOT NULL
);
DENY SELECT ON automation_meta.forward_target TO public;
GO
INSERT INTO automation_meta.forward_target (id, url) VALUES (1, N'<your SQL Server ingress URL>');
GO
CREATE OR ALTER PROCEDURE automation_meta.forward_changes
  @operation NVARCHAR(10),
  @schema SYSNAME,
  @table SYSNAME,
  @rows NVARCHAR(MAX),
  @previous_rows NVARCHAR(MAX) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT OFF;
  DECLARE @url NVARCHAR(4000) = (SELECT url FROM automation_meta.forward_target WHERE id = 1);
  IF @url IS NULL RETURN;
  DECLARE @row_count INT = (SELECT COUNT(*) FROM OPENJSON(ISNULL(@rows, N'[]')));
  -- Webhook bodies are capped at 1 MB: a statement too large to send says so instead.
  DECLARE @truncated BIT = IIF(DATALENGTH(@rows) + ISNULL(DATALENGTH(@previous_rows), 0) > 900000, 1, 0);
  DECLARE @payload NVARCHAR(MAX) = (
    SELECT
      @operation AS operation,
      DB_NAME() AS [database],
      @schema AS [schema],
      @table AS [table],
      JSON_QUERY(IIF(@truncated = 1, N'[]', ISNULL(@rows, N'[]'))) AS rows,
      JSON_QUERY(IIF(@truncated = 1 OR @previous_rows IS NULL, NULL, @previous_rows)) AS previousRows,
      @row_count AS [rowCount],
      CAST(@truncated AS BIT) AS [truncated],
      CONVERT(NVARCHAR(33), SYSUTCDATETIME(), 127) + 'Z' AS changedAt
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );
  DECLARE @status INT, @response NVARCHAR(MAX);
  BEGIN TRY
    -- A slow or unreachable endpoint never blocks or undoes the write.
    EXECUTE @status = sp_invoke_external_rest_endpoint
      @url = @url, @method = 'POST', @payload = @payload, @timeout = 5,
      @response = @response OUTPUT;
  END TRY
  BEGIN CATCH
    RETURN;
  END CATCH
END;
GO

-- 3. Once per table you want to watch. Replace dbo.orders (four places).
CREATE OR ALTER TRIGGER dbo.orders_forward_changes ON dbo.orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT OFF;
  DECLARE @has_inserted BIT = IIF(EXISTS (SELECT 1 FROM inserted), 1, 0);
  DECLARE @has_deleted BIT = IIF(EXISTS (SELECT 1 FROM deleted), 1, 0);
  IF @has_inserted = 0 AND @has_deleted = 0 RETURN;
  DECLARE @operation NVARCHAR(10) =
    CASE WHEN @has_inserted = 1 AND @has_deleted = 1 THEN N'update'
         WHEN @has_inserted = 1 THEN N'insert' ELSE N'delete' END;
  DECLARE @rows NVARCHAR(MAX), @previous NVARCHAR(MAX);
  IF @operation = N'delete'
    SET @rows = (SELECT * FROM deleted FOR JSON PATH, INCLUDE_NULL_VALUES);
  ELSE
    SET @rows = (SELECT * FROM inserted FOR JSON PATH, INCLUDE_NULL_VALUES);
  IF @operation = N'update'
    SET @previous = (SELECT * FROM deleted FOR JSON PATH, INCLUDE_NULL_VALUES);
  EXECUTE automation_meta.forward_changes
    @operation = @operation, @schema = N'dbo', @table = N'orders',
    @rows = @rows, @previous_rows = @previous;
END;
GO

One statement is one run. The payload carries operation (insert, update, or delete), database, schema, table, rows (every column of each changed row; for a delete, the rows as they were), previousRows (for an update, the same rows before the change), rowCount, truncated, and changedAt. A statement that changes several rows sends them together in rows, so add a Loop node to handle them one at a time. All your tables post to the same ingress URL, so a workflow receives the changes of every table you forward. When you forward more than one table, add a Filter node on table right after the trigger.

The forwarding runs inside the statement that changed the rows, so it adds the time of one HTTPS call to each insert, update, or delete on the table. If TaskJuice cannot be reached within 5 seconds, the call is abandoned and the write still succeeds, but that change does not start a run. To stop forwarding a table, run DROP TRIGGER dbo.orders_forward_changes;. To stop forwarding everywhere, delete the row in automation_meta.forward_target.

Actions

• Find Rows finds rows in a table without writing SQL. Filter with conditions such as status = open or total >= 100 (operators =, !=, >, >=, <, <=, like, not-like, in, not-in, is-null, is-not-null), match all or any of them, pick the columns to return, sort, and cap the row count.
• Get Row gets one row by its primary key, for example { "id": 42 }, and returns it with found: true, or found: false and row: null when no row has that key. Give every column of a composite key; a key that matches more than one row fails the step.
• Run Query runs a read-only SELECT with @p1, @p2, … placeholders and a matching params array, returning the result rows and the row count.
• Insert Row inserts a row from a column-to-value map.
• Insert Rows inserts up to 1,000 rows in one statement from an array of column-to-value objects, such as the items of a trigger. A column missing from a row is inserted as NULL.
• Update Rows updates the rows matching an equality filter, setting new column values, and returns the affected row count.
• Upsert Row inserts a row or, on a match with the columns you name, updates it using a parameterized MERGE.
• Delete Rows deletes the rows matching an equality filter and returns the deleted row count.
• Execute SQL runs one SQL statement of any kind: an UPDATE with a custom WHERE, an EXEC of a stored procedure (its first result set is returned as rows), or a schema change. Treat it with care; it can change or remove data and cannot be undone.

Insert Row, Insert Rows, and Upsert Row return the whole written rows, every column as stored, so a later step can use a generated id or any default the database filled in, for example {{ $steps.insert_1.data.rows[0].id }}. Tables with triggers, including the forwarding trigger above, return the rows too. The rows come back empty only when the database user has no SELECT on the table; the write itself still happens. When every value you upsert is one of the matched columns, an existing row is read back through a no-op update. If that update is refused (for example, the user has no UPDATE on the table), the upsert still runs and the rows come back empty, for a new key too.

Write a safe query

Always pass values through params, never by building the SQL string yourself:

sql:    SELECT id, email FROM customers WHERE status = @p1 AND created_at > @p2
params: ["active", "2026-01-01"]

Table and column names in the structured actions and triggers are validated against a strict allowlist and quoted for you, so a name containing a bracket, semicolon, or whitespace is rejected before anything runs. Run Query, the custom-query triggers, and Execute SQL accept one statement at a time. SQL Server runs a whole batch when a single EXEC runs a string or sp_executesql, so the login the connection uses is what limits what Execute SQL can do: give it only the permissions the workflow needs.

Known limitations

• Reads return at most 1,000 rows and around 5 MB per call; when a read is clipped the result sets truncated to true. Use TOP and a WHERE clause to keep reads bounded.
• A statement that runs longer than 30 seconds is cancelled by the database. Keep queries indexed and selective.
• Each action and each interval check opens its own short connection and closes it when done.
• The interval triggers check on an interval rather than reacting instantly. Keep the watched columns indexed; New or Updated Row reads best with a composite index on the timestamp and ID columns.
• New or Updated Row tracks its position to the millisecond and reads rows that share a millisecond in ID order, so every row is delivered exactly once at any column precision, even when one bulk UPDATE stamps thousands of rows with the same time. A datetime column's 1/300-second ticks and a datetimeoffset column's offsets are both handled.
• The ID column of New Row and the timestamp and ID columns of New or Updated Row must never be NULL. Declare them NOT NULL.
• The instant triggers require SQL Server 2025. Azure SQL Database and Azure SQL Managed Instance only allow sp_invoke_external_rest_endpoint to call Azure services, so they cannot reach TaskJuice directly; use the interval triggers there.
• An instant trigger sends at most about 1 MB per statement. A statement that changes more data than that sends rows: [] with truncated: true and the rowCount, so a workflow can tell and read the rows itself.
• In the instant payload, datetime2 and datetime values are written as SQL Server stores them, without a time zone.
• New Table and New Column watch the login's default schema only.