SQL Server probe

The SQL Server probe runs a T-SQL batch or a stored procedure and records what came back. A batch that returns three result sets gives you three, and a procedure that raises an informational message or returns a value through an OUT parameter gives you those too, each one something a later step can read and assert on.

Engineering tier

The SQL Server probe is available on Engineering tier and above.

Connection settings

Field	Description
Host	Hostname or IP of the SQL Server instance
Port	Server port (default: 1433)
Database	Database to connect to
Instance name	A named instance. Leave blank for the default instance
TLS mode	Disabled, Required, Verify CA or Verify certificate and hostname
Credential	A Basic credential from the credential store
Max rows	The row cap for a result set. Default 1000

Instance name is the field with no counterpart on the other databases. A named instance replaces the port in the connection, and resolving it goes through the SQL Browser service on UDP 1434, so getting it wrong looks like a network problem rather than a typo. Leave it blank unless you are connecting to a named instance.

The query

Write a T-SQL batch, or call a procedure. Send with Ctrl+Enter or the send button.

Values from the active environment resolve into the text before it is sent, so a query can carry {{orderId}} the way any other probe does.

Below the query, Procedure parameters binds what a procedure takes and returns. Each row has a direction:

Direction	Meaning
IN	A value you supply
OUT	A value the procedure returns
INOUT	Both: you supply one and the procedure replaces it

Values are bound rather than pasted into the text, so a value carrying a quote is a value and not a syntax error. A row you switch off is left out of the call rather than sent empty, and order matters, because a procedure’s signature is positional; the arrows on each row move it.

An unnamed parameter’s output comes back under its position among the rows you send, which is what the greyed-out name shows you. The same panel is on the PostgreSQL and MySQL probes.

What comes back

A batch can produce more than one kind of output, and the probe keeps all three.

Result sets. Every result set in the batch, in order, not only the first. A procedure that selects twice gives you two blocks, one tab each. A single result gets no tabs.

Server messages. Anything the server printed, from PRINT or from RAISERROR at severity 10 or below. These are informational, so the probe still succeeds; severity above 10 is an error and lands in the error fields instead. They appear under the results, and an ordinary query leaves the block absent, which is correct and is also indistinguishable from a probe that never ran, so assert on something else as well.

OUT parameters. The values the procedure returned, shown beside the row that asked for them and again under the results.

The row cap

A result set is cut to Max rows and the response says so. rowCount is then the number of rows you received rather than the number the table holds, because the cap is applied at the driver and the query stops early, so the true total is never read and the probe does not claim one.

That is the honest answer rather than a limitation to work around: a careless SELECT * against a real table cannot pull the whole thing into memory. When a chain needs to know that nothing was cut off, assert on MSSQL_TRUNCATED rather than comparing row counts.

Extractors

Extractor	Pulls out
MSSQL_SUCCESS	Whether the probe succeeded
MSSQL_COLUMN	One column value from a result set
MSSQL_ROW_COUNT	Rows returned
MSSQL_RESULT_COUNT	How many result sets came back
MSSQL_AFFECTED_ROWS	Rows an INSERT, UPDATE or DELETE touched
MSSQL_OUT_PARAM	One OUT parameter, by name
MSSQL_MESSAGES	What the server printed
MSSQL_TRUNCATED	Whether the row cap was reached
MSSQL_ERROR_CODE	The message number, for example 208 for an invalid object name
MSSQL_SQL_STATE	The SQLSTATE
MSSQL_JSON	A value out of a JSON column

MSSQL_ERROR_CODE reads the message number, not the SQLSTATE, because that is what every Microsoft reference is indexed by: an invalid object name is 208, and its SQLSTATE is the much coarser S0002. MSSQL_SQL_STATE is there for a chain that has to run against more than one database engine, where SQLSTATE is the only code they share.

Chains

A SQL Server step works like any other: extract a value, feed it into the next step, assert on it. See chain steps and extractors.

A common shape is to do something over HTTP and then check the database agrees: create an order through the API, then read it back with a query and assert the row is there with the status you expect. The API saying 201 and the row existing are two different claims.

A local server to test against

The bundled docker targets include mssql-simple on port 1433, seeded with tables and four stored procedures that exercise the output this probe reports: an OUT parameter, an INOUT parameter, a procedure that prints a message, and one that returns two result sets.

cd virtuprobe-docker/mssql-simple && docker compose up -d

Two requirements that look like something else

The image needs 2 GB of RAM and runs on x86-64 only, with emulation unsupported. Below the memory floor it exits during startup, which reads as a configuration fault rather than a resource one.