SQL Server Execute Query

Read-only SQL query tool for Microsoft SQL Server exposed through the Agents Platform.

This document covers mssql_execute_query, the read-only SQL query tool for Microsoft SQL Server exposed through the Agents Platform.

Authentication

mssql_execute_query connects to a customer’s own SQL Server (SQL Authentication only — username/password, not Windows/Azure AD auth) via the generic integrations config form. Users configure:

• host, port (default 1433), database, username, password
• encrypt — disable, true (TLS required), or strict (TDS 8.0, verify certificate). Default true.
• trust_server_certificate — trust the server’s TLS certificate without CA verification. Default false. Enable this for an on-prem server with a self-signed certificate (encrypt: true + trust_server_certificate: true); otherwise the connection fails with a certificate-verification error.
• host_name_in_certificate — optional, only relevant when validating a certificate whose SAN doesn’t match the connection host.
• schema — optional namespace. Leave empty to use the default dbo schema.

All of these fields are injected by the platform from the stored integration at call time (x-variable-service: "mssql") — they are not entered per run.

mssql_execute_query

Executes a read-only SQL query against a SQL Server database and returns the results as structured rows.

Inputs

Required:

• query: SQL query to execute. SELECT statements only — see below.

Optional:

• max_rows: Maximum rows to return (defaults to 3000 when unset or 0; a caller-supplied value above 3000 is honored as-is, not clamped).
• query_params: values for a pinned query’s named placeholders. Supplied by the agent per call — it is not a configurable field and does not appear in the tool’s configuration dialog. It is offered to the agent only when query is pinned to a statement containing placeholders — see below.

Pinned queries with named parameters

Instead of letting the agent write the SQL on every run, set the tool’s Query field in its configuration dialog to a fixed statement that addresses its values by name:

SELECT TOP 50 Id, PaymentTerms
FROM dbo.Vendors
WHERE Name = :vendor_name
  AND OrganisationId = :org_id

The tool then offers the agent a query_params object whose keys are exactly the placeholders that query declares, and the agent only supplies values:

{ "query_params": { "vendor_name": "O'Brien Supply Co.", "org_id": "3f2a…" } }

Placeholders are rewritten to SQL Server’s @name form and bound via sql.Named, so a value containing an apostrophe needs no escaping and cannot change the statement. A query is only ever inspected for placeholders on a call that carries query_params; a query the agent writes itself is passed to the database exactly as written. Prefer this over a model-authored query for any lookup that runs in production: the SQL is reviewed once, at configuration time, instead of being regenerated on every task.

Notes:

• Write placeholders as :name even though SQL Server’s own parameter syntax is @name — the platform rewrites them. An existing @variable in the query is left untouched.
• Repeat a name to reuse the same value: WHERE Name = :n OR Alias = :n binds once.
• Cast in the SQL, and treat this as required rather than stylistic on SQL Server. Every bound value is sent as a Go string, which go-mssqldb transmits as NVARCHAR. Against a VARCHAR column, SQL Server’s data-type precedence converts the column rather than the parameter, which can turn an index seek into a scan under a SQL collation — where the previous inline literal seeked. And a non-string column rejects the text outright: a bit column bound with "true" fails with Conversion failed when converting the nvarchar value 'true' to data type bit. Write WHERE Total = CAST(:amount AS DECIMAL(18,2)) and WHERE Name = CAST(:vendor_name AS VARCHAR(100)). (Postgres is unaffected — pgx sends text-format parameters and the server parses each per its inferred type.)
• Placeholders substitute values, never table or column names, and cannot supply a list to IN (...).
• A bracketed identifier ([Order Details]) is treated as opaque, so a colon inside one is not mistaken for a placeholder.
• Maximum 64 distinct placeholders. A pinned query that exceeds this, or that cannot otherwise be scanned, is rejected when the tool is saved, and again at run time.
• $ is an ordinary identifier character here (a$b$c), not the start of a quoted block as it would be in Postgres.
• Stacked-statement rejection (below) still applies to a pinned query.

SELECT-only enforcement

Only statements starting with SELECT (after trimming whitespace) are accepted; anything else is rejected before a connection is even opened. Use mssql_upsert for writes — its query field accepts arbitrary INSERT/UPDATE/DELETE/MERGE statements when the structured upsert path can’t express what you need.

Schema qualification

SQL Server has no search_path equivalent, so unlike some other database connectors this tool never sets a session-level default schema. Reference tables with the schema explicit in the query (SELECT * FROM dbo.orders or SELECT * FROM sales.orders) — the configured schema field only affects credential defaults surfaced elsewhere (e.g. mssql_upsert’s generated statements), not what you write in a raw query here.

Output

On success, structured content includes:

• columns: column names, in result order
• rows: array of column → value objects
• row_count: number of rows returned (after truncation)
• truncated: true if more rows existed than max_rows allowed
• database, host: the database/host queried
• max_rows: the effective cap applied
• query: the SQL as authored, with its :name labels intact — this is what the task feed renders
• query_bound: the statement actually sent, with :name rewritten to @name
• query_param_keys: the placeholder names that were bound (never the values, which are customer data)
• param_count: how many parameters were bound

Expected errors

• Missing or invalid integration credentials.
• A non-SELECT statement, or more than one statement.
• A placeholder in the pinned query with no value in query_params, or a query_params key that is not a placeholder in the query — both name the offending key and list the placeholders the query expects, so the agent can retry.
• A query_params value that is an object or an array (numbers and booleans are accepted and sent as text).
• A query declaring placeholders on a call that supplied no query_params at all — reported with the placeholder names, rather than reaching the database with @name markers intact.
• A SQL error from the database (surfaced as a tool error, not a panic).

Notes

• A uniqueidentifier column comes back as a standard GUID string (e.g. "01234567-89AB-CDEF-0123-456789ABCDEF"), not raw bytes — the tool decodes SQL Server’s internal mixed-endian byte layout for you.
• Every query execution emits a HIPAA audit event (external_call, provider mssql) recording the host, latency, and outcome — see SQL Server Upsert for the same on the write path.