SQL Agent

The SQL Agent is the agent on the SQL Workbench page. It writes and explains statements in the ToolBox SQL dialect, puts them in the statement editor, executes them through the ToolBox SQL engine, shows the result in the grid, and works with your saved SQL statements. This page describes what it operates on, what it knows about the dialect, how it works, and how to use it well.

For the panel itself (sending messages, tool indicators, files, conversations) see Using the Agent Panel.

What the Agent Operates On

Object	What the agent can do
The statement editor	Read the current statement, replace it
The SQL engine	Execute the editor statement (result shown in the grid) or a probe statement (result returned to the model only)
The result grid	Shows the result of an editor statement the agent executed
Saved statements	List, read, load into the editor, and save with a name and description
The data model	Find tables by name, caption, or number; read a table's fields, types, option members, and keys; read sample rows

It has the same data-model discovery tools as the coding agent but none of the AL editor tools: it cannot write or run programs.

How It Sees Your Statement

Its instructions tell it to call get_sql first. That tool returns:

the statement in the editor right now, including your own edits;
the statement name when the statement was loaded from or saved to the saved statements;
the last run: the statement that was executed, whether it succeeded, and a one-line summary ("42 rows returned", "3 rows affected", or "failed: " followed by the engine's error).

The last run is recorded both when you use Execute SQL yourself and when the agent executes the editor statement. "Why did this fail?" therefore works on your own executions too.

What It Knows About ToolBox SQL

The agent's instructions summarise the dialect so it does not write SQL Server syntax the engine rejects:

Statements. SELECT with TOP, DISTINCT, joins, WHERE, GROUP BY, HAVING, ORDER BY, OFFSET ... FETCH; INSERT ... VALUES with one or more rows; UPDATE ... SET with literals; DELETE; UNION, UNION ALL, INTERSECT, EXCEPT. There is no LIMIT.
Identifiers. Table and field names or captions, case-insensitive, in [brackets] when they contain spaces or punctuation; tables can also be addressed by number.
Literals. Single-quoted text with '' escaping, plain numbers with a . decimal separator, ISO dates, booleans as 'true'/'false' or 1/0, options by name, caption, or ordinal.
WHERE. The comparison operators, LIKE, IN (list or uncorrelated subquery), BETWEEN, IS [NOT] NULL, [NOT] EXISTS, AND/OR/NOT, field-to-field comparison; no arithmetic on the left side of a comparison.
Joins are lookup joins. INNER/LEFT JOIN pairs each main row with the first matching joined row and never multiplies rows; CROSS JOIN of exactly two tables is the only row-multiplying join; no RIGHT/FULL JOIN, no derived tables. The agent knows which conditions belong in ON (field equalities and literal filters on the joined table) and which in WHERE.
Aggregates only. COUNT, SUM, AVG, MIN, MAX are the only functions; no string or date functions, no CAST. Computed columns allow arithmetic and CASE.
FlowFields are calculated automatically; FlowFilter fields cannot be queried.
Caps. Results stop at the Max Rows setting (default 1000); TOP lowers but cannot raise the cap. Unknown tables and columns are errors, never ignored.
Writes go through the normal Business Central record path (validation and table triggers), values must be literals, and there is no undo.

The full grammar is in the SQL Syntax Reference.

The Agent's Working Method

Understand. Call get_sql; read the last run when asked about a failure.
Discover. Never guess a table or field name: find_objects for the table, get_table_info for the exact field names, types, option members, and keys, read_table_rows to see real values and formats.
Write. Put the finished statement in the editor with set_sql so you can run, tweak, and export it. Before doing so, re-check every ON clause. Keep queries selective: name the columns, add WHERE and TOP, aggregate instead of listing whole tables. Reuse saved statements as examples of house style; save only when asked.
Run. run_sql executes the editor statement and shows the result in the grid; a sql parameter runs a probe that stays out of the editor and the grid. On failure, read the error, fix, and retry a few times at most. Before any INSERT, UPDATE, or DELETE, run the SELECT with the same WHERE, show what it hits, and proceed only when you confirm. Never run an UPDATE or DELETE without a WHERE clause.
Explain briefly. What the query does and what the result shows, quoting only the few numbers that matter.

What Happens on the Page

Tool	Effect you see
`set_sql`	The statement editor shows the new statement within about two seconds. It is not executed.
`run_sql` (editor statement)	The grid shows the result (or clears on failure). The last run is recorded. The tool result carries the columns, up to `max_rows` sample rows (default 25, at most 100), and the full row count.
`run_sql` with a `sql` parameter	Nothing visible. The probe result goes to the model only.
`open_sql_statement`	The editor loads the saved statement and adopts its name, so Save overwrites it.
`save_sql_statement`	The statement is saved (created or overwritten), and the page adopts the name.
`get_sql`, `list_sql_statements`, `get_saved_sql`, `find_objects`, `get_table_info`, `read_table_rows`	Nothing visible.

Row caps apply in two places. The engine caps the result at Max Rows (ToolBox Setup, SQL group; default 1000). The tool result additionally shows only a sample of those rows to the model, but always reports the full count and says when the engine cap was hit. The grid shows everything the engine returned.

Data Changes

run_sql can execute INSERT, UPDATE, and DELETE. They run with your permissions through the normal record path, so validation and table triggers fire and there is no undo.

The agent's instructions require it to select first and confirm with you. A well-behaved exchange looks like this:

You: "Set Blocked to All on customers with no sales in the last two years."
Agent: looks up the fields, runs a probe SELECT with the intended WHERE, and reports "This affects 17 customers: ... Shall I run the UPDATE?"
You: "Yes."
Agent: puts the UPDATE in the editor, executes it, reports "17 rows affected."

If the agent skips step 2, tell it to show you the rows first. Read the statement in the editor before confirming; the WHERE clause is what protects your data.

Example Requests

Build a query

Show the ten customers with the highest balance, with their salesperson.

Expect get_sql, find_objects/get_table_info for Customer, set_sql, and, if you asked to run, run_sql. The statement uses TOP 10, brackets around [Balance (LCY)], and ORDER BY ... DESC.

Join headers and lines

List open sales orders with their first line's item and quantity.

Expect a lookup join from [Sales Header] to [Sales Line] with the Document Type filter in the ON clause and an explanation that the join returns the first matching line per order.

Aggregate

How many posted sales invoices per month this year, with total amount?

Expect a GROUP BY on a date part expression if the engine allows it, or an explanation of what the engine can and cannot group on, followed by the closest working statement.

Explain an error

My statement fails with "unknown column". What's wrong?

Expect get_sql (reads the last run's error), a get_table_info to compare names, and a corrected statement in the editor.

Explore the schema

Which tables hold warehouse shipments and how are they related?

Expect several find_objects and get_table_info calls and a short explanation with the key fields. Nothing changes in the editor unless you ask for a query.

Reuse a saved statement

Take my saved "Open Orders" statement and restrict it to location BLUE.

Expect list_sql_statements or get_saved_sql, open_sql_statement, set_sql.

Tips

Ask for TOP and columns. The agent does this by default; if it lists whole tables, remind it.
Use the grid, then Excel. Once the statement in the editor returns what you want, use Export to Excel on the page for the full result.
Say "probe" when you only want a number. "How many..." questions are answered with a probe that stays out of your editor.
Save good queries. "Save this as Monthly Sales" stores it for you and for future agent conversations, which are told to reuse saved statements as examples.
Confirm writes deliberately. Read the WHERE clause in the editor before answering yes.
Explain the dialect to yourself. "Why can't I use DATEADD here?" gets you the engine's rule and a workaround.