Microsoft SQL

Read and write a Microsoft SQL Server database from a workflow: Azure SQL Database, SQL Server on premises or in a VM, and Azure SQL Managed Instance.

The Module adds four Tasks: Query rows, Run statement, Call stored procedure and Insert rows. Each Environment points at one database with its own credentials, so a Test Environment can work on a copy while Production works on the real one.

What you can build

• Look a customer up: Query rows with First row finds the record whose email matches {email} and gives you {rows.Name} and {rows.Status} for the conversation.
• Save a lead: Run statement inserts the form's fields and returns the new id with OUTPUT INSERTED.Id, or Insert rows writes {form.fields} straight into the table.
• Load a spreadsheet: File Query reads an uploaded Excel file as rows, Insert rows writes them into a staging table in one transaction.
• Report: Query rows with All rows feeds a list to File Query (an Excel attachment) or to an AI task (a summary).
• Business logic that lives in the database: Call stored procedure runs it with named parameters and reads its output parameters and return value.

Requirements

• A SQL Server the engine can reach over the network (TCP 1433, or the port you choose). Azure SQL: allow the engine's outbound addresses in the server firewall, or use a private endpoint.
• A login with the least rights the flows need (db_datareader for queries, INSERT/UPDATE on the tables they change, EXECUTE on the procedures they call).

Configuration

Setting	Required	Notes
Server	yes	Host name, with the port after a comma when it is not 1433 (sql.example.com,1434) or a named instance after a backslash (sql.example.com\SALES).
Database	yes	The database to open.
Sign in with	yes	SQL Server login: a user name and password created in the database. Microsoft Entra ID user: an Entra account (name@domain) and its password. Microsoft Entra ID service principal: an Entra application's client id and client secret.
User name / Password	yes	The credentials of the chosen sign-in. Stored securely.
Encryption	no	Required (TLS) is the default and what Azure SQL needs. Strict (TDS 8.0) for SQL Server 2022+ configured for it. If the server offers it only for an old server on a private network.
Trust the server certificate	no	On for a self-signed certificate on a server you control. Keep it off for Azure SQL.
Command timeout (seconds)	no	1–300. Empty: 30. A Task can set its own.
Connection string	no	Advanced: a full connection string instead of the fields above. It must carry its own credentials: Integrated Security and managed identities are refused, since they would use the engine's own identity.

The Module never uses the engine's identity to sign in, and refuses servers on private networks (loopback, 10.x, 192.168.x, 172.16–31.x, link-local) and local instances unless the platform operator allows them (MsSql:AllowPrivateNetworks in the engine's configuration; MsSql:AllowIntegratedSecurity for LocalDB in local development).

Variables in SQL

Write {variables} in the SQL where a value goes. They are passed to the server as parameters, never pasted into the text, so whatever a visitor typed can only be compared, never executed:

SELECT Id, Name, Email FROM dbo.Customers WHERE City = {form.fields.city} AND Created >= {since}

• A value keeps its type: a number stays a number, a date a date, text stays text. Text from a form is text; the server converts it to the column's type when it compares ('12' to an int column works).
• A variable that is not set is NULL. WHERE Email = {email} matches nothing when {email} is empty; use WHERE ({email} IS NULL OR Email = {email}) for an optional filter.
• A variable holding a list of plain values becomes a list of parameters: WHERE Id IN ({ids}) runs as IN (@p0, @p1, @p2). An empty list runs as IN (NULL), which matches nothing.
• A variable holding a record or a list of records travels as JSON text: SELECT * FROM OPENJSON({order.lines}) WITH (Sku nvarchar(50), Qty int).
• {execution.now} and {execution.today} work too: WHERE Due < {execution.today}.
• Braces that are not a variable ('{"a":1}', {{) are left alone.

Names (tables, columns, procedures) cannot be parameters. The Task fields that take a name (Table, Procedure) accept {variables} as templates and quote the result as [schema].[name]; the SQL fields do not substitute names.

Query rows

Runs a SELECT (or any query that returns rows) and puts the rows in a variable.

Property	Notes
SQL	The query, in T-SQL, with {variables} as parameters.
Variable gets	All rows: a list of records ({rows.0.Name}, or a loop). First row: one record ({rows.Name}). Single value: the first column of the first row (SELECT COUNT(*) …).
Timeout (seconds)	1–300. Empty: the configuration's default.
Empty result	Ok: the variable is an empty list, an empty record or null. Error: the Task takes its error flow with {sqlError.code} = no_rows.
Rows variable	Where the rows go. Default {rows}.
Store result in	{result.rows} (the count), {result.columns} (the names, comma separated), {result.durationMs}.

At most 20,000 rows and 8 MB go into variables; a larger result fails with too_many_rows / too_large: add TOP or a filter. Unnamed columns are called column1, column2…; a duplicate name gets a number.

Values: numbers, text, bit as true/false, dates as date-times; uniqueidentifier as text, varbinary as base64, time as hh:mm:ss.

Run statement

Runs an INSERT, UPDATE, DELETE, MERGE or a whole batch, and reports the rows affected.

Property	Notes
SQL	The statement(s), with {variables} as parameters. Several statements separated by ; run as one batch.
Run in a transaction	On (default): the batch is committed at the end or rolled back on an error. Off: each statement commits as it runs.
Timeout (seconds)	1–300. Empty: the configuration's default.
No row affected	Ok: {result.affected} is 0. Error: the Task takes its error flow with no_rows (an UPDATE whose WHERE matched nothing).
Returned row	The first row the batch returns, when it returns one: INSERT … OUTPUT INSERTED.Id VALUES (…) gives {returned.Id}; SELECT SCOPE_IDENTITY() AS Id works too. Empty: not kept.
Store result in	{result.affected} (the rows changed; -1 when the batch starts with SET NOCOUNT ON), {result.durationMs}.

INSERT INTO dbo.Leads (Name, Email, Source)
OUTPUT INSERTED.Id, INSERTED.Created
VALUES ({form.fields.name}, {form.fields.email}, 'website')

Call stored procedure

Calls a procedure with named parameters and collects its first result set, its output parameters and its return value.

Property	Notes
Procedure	schema.name (dbo.usp_GetOrders); supports {variables}.
Parameters	One row per parameter: Name (with or without @), Value (supports {variables}; a value that is exactly one {variable} keeps its type, other text is a template; empty is NULL), Direction (In, Out, In/Out), Type (Auto, Text, Integer, Number, True/false, Date & time, Guid). Set the Type on output parameters so the value comes back as the right type.
Rows variable gets	What to keep of the first result set: All rows, First row, Single value, or Nothing.
Outputs variable	The output parameters by name ({outputs.Total}) and the return value ({outputs.returnValue}).
Store result in	{result.returnValue}, {result.rows}, {result.affected}, {result.durationMs}.

Insert rows

Inserts the records a variable holds into a table, by bulk copy, in one transaction: either every row goes in or none does.

Property	Notes
Table	schema.table; supports {variables}.
Rows	One {variable} holding one record ({form.fields}) or a list of records ({rows} from Query rows, the rows of a Data query, a CSV read with File Query).
Columns	Only these fields of the records are inserted, comma separated. Empty: every field. A field missing from a record is NULL (or the column's default).
Ignore unknown fields	On: a field the table has no column for is skipped. Off (default): it fails the Task with invalid_input, naming the fields.
Keep identity values	On: a record's value for an IDENTITY column is written as it is. Off: the server numbers the rows.
Fire triggers	On: the table's INSERT triggers run. Off (default): a bulk insert skips them.
Batch size	Rows sent at a time, 1–10000. Empty: 1000.
Store result in	{result.inserted}, {result.columns}, {result.durationMs}.

Fields are matched to columns by name, any case. Values are converted to the column's type by the server's rules ("12" into an int column, an ISO date into a datetime2); a value that does not fit fails the whole insert with invalid_data. Check constraints are always enforced. At most 100,000 rows per Task.

Errors

Every Task takes its error flow when something goes wrong, and writes {sqlError}:

{sqlError.code}	When
invalid_input	The Task is not set up correctly: empty SQL, a bad variable, an invalid table name, an unknown field.
missing_configuration	The Environment's connection is missing or not allowed.
connection_failed	The server could not be reached, or is on a private network the engine may not reach.
login_failed	The server refused the login, or the database does not exist for it.
permission_denied	The login may not do this.
invalid_sql	The SQL could not be parsed, or names an object or column that does not exist.
invalid_data	A value could not be converted to the column's type, or is too long for it.
duplicate	A unique or primary key already holds the value.
constraint	A foreign key, check or NOT NULL constraint refused the change.
deadlock	The statement was chosen as a deadlock victim; it can be retried.
timeout	The command took longer than its timeout.
raised	The procedure or statement raised its own error (THROW / RAISERROR with a number of 50000 or more); {sqlError.detail} is its text.
no_rows	No row came back, or no row was changed, and the Task was told to fail then.
too_many_rows, too_large	The result does not fit in a variable.
sql_error	Any other server error.

{sqlError.message} is a short sentence safe to show an end user; {sqlError.detail} is the server's own text, for the author; {sqlError.number} is the SQL Server error number (0 when the error did not come from the server). A Switch on {sqlError.code} sends duplicate to a "you are already registered" reply and everything else to a generic failure.

Log messages never include the password or the connection string.