FlowmageDocs
SQL Server

Run SQL query

BetaChanges data

Reads or changes rows in a SQL Server or Azure SQL database with SQL you write.

Settings

SettingWhat it is
ConnectionRequiredThe SQL Server connection the step signs in with.
OperationSQL Server cannot enforce read-only per query — use a login that can only read.
QueryRequiredWrite values from earlier steps as @p1, @p2 … and list them under Parameters. {{ }} is not filled in here.
ParametersOne value per placeholder, in order. Wrap JSON for a json column with OPENJSON(@p1).
Max rowsDefault 1,000 — up to the workspace limit. On SQL Server, limit with TOP instead of LIMIT.
If more rows"fail" or "first"
Technical details
KeyValue
connectiontexta connection id from `requires.connections` (provider `mssql`)
operation"select" or "execute", by default "select"
querytextplain text, never `{{ }}`
paramslist of any JSON, by default []
maxRowsnumber, by default 1000
ifMoreRows"fail" or "first", by default "fail"used when `operation` is "select"

Output

FieldType
rowsarray
rowCountRows returnednumber
columnsColumns — name and typearray
truncatedMore rows than Max rows — only the first came backboolean
affectedRowsRows the statement changednumber
Sample output, and the expression for each field
  • columns
    • [0]
      • dbType"int"
      • name"id"
      • type"integer"
    • [1]
      • dbType"varchar"
      • name"email"
      • type"text"
  • rowCount1
  • rows
    • [0]
      • email"ada@example.com"
      • id7
  • truncatedfalse

Errors

When a step of this node stops a run, the run page shows a code. Look it up:

Common errors

93 codes

Example step

The step as a flow file holds it. Replace each value in <…>.

{
  "config": {
    "connection": "<connection id>",
    "ifMoreRows": "fail",
    "maxRows": 1000,
    "operation": "select",
    "params": [],
    "query": "<query>"
  },
  "label": "Run SQL query",
  "type": "mssql.query",
  "version": 1
}