SAP B1 Service Layer SQLQueries: how to run SQL, and how to fix errors 701 and -2028

Why SQLQueries exists

The Service Layer exposes business objects such as Orders and Invoices. That is fine for creating documents, but reporting needs joins and grouping across many rows. Pulling every invoice line through OData and grouping it in your app is slow.

SQLQueries solves this. You save a SELECT statement in SAP Business One once, then run it over HTTPS whenever you need it. We use it in the Kodenova SAP B1 dashboard to read a year of sales in a few calls.

Step 1: Save the query

POST https://your-b1-server:50000/b1s/v1/SQLQueries
Content-Type: application/json

{
  "SqlCode": "KN_SALES_01",
  "SqlName": "Sales lines since a date",
  "SqlText": "SELECT T0.\"DocEntry\", T0.\"DocDate\", T0.\"CardCode\", T1.\"ItemCode\", T1.\"LineTotal\" FROM OINV T0 INNER JOIN INV1 T1 ON T0.\"DocEntry\" = T1.\"DocEntry\" WHERE T0.\"CANCELED\" = 'N' AND T0.\"DocDate\" >= :dfrom"
}

A successful call returns 201 Created. SqlCode is the key you call later, so keep it short and stable.

Step 2: Run it with List

POST https://your-b1-server:50000/b1s/v1/SQLQueries('KN_SALES_01')/List
Content-Type: application/json

{ "ParamList": "dfrom='2025-01-01'" }

The response has a value array with your rows. Large results come in pages: follow odata.nextLink (v1) or @odata.nextLink (v2) until it disappears.

Several parameters are joined with &:

{ "ParamList": "dfrom='2025-01-01'&card='C0001'" }

Error 701: invalid SQL syntax

A typical message looks like this:

[701] Invalid SQL syntax:, line 1, character position 280,
no viable alternative at input 'T0."DocDate" >= :from'

This is the one that cost us an afternoon. The SQL was valid, but the parameter was called :from. The parser read it as the FROM keyword. Renaming it to :dfrom fixed it at once.

Other causes of 701:

• Using SQL Server syntax on a HANA company, or the other way round. Double-quote identifiers and match the database dialect.
• Expressions the allow-list does not accept, such as some CASE or function calls. Select raw columns and calculate in your app.
• A trailing semicolon or a comment inside SqlText.

Error -2028: no matching records found

You call SQLQueries('KN_SALES_01')/List and get -2028. The query is not in that company. Common reasons:

• The company database was restored or copied without your saved queries.
• Someone deleted the query, or another app used the same SqlCode.
• You logged in to a different company than the one you saved the query in.

A robust integration handles this itself: on -2028, POST the query again, then retry List once. We name codes with a short hash of the SQL text, so a changed query gets a new code and never runs stale SQL.

Allow-list and permissions

SQLQueries does not run any SQL on any table. It checks the statement against an allow-list on the Service Layer server. If a table is not allowed, the call fails even though the SQL is correct. Ask your SAP administrator to review the allow-list before you design the report around a blocked table.

The user also needs two authorizations:

• Modify SQL Queries in Service Layer: to create, change and delete queries.
• Service Layer SQL Query: to execute a specific query.

Checklist

1. Keep SqlText simple: raw columns, joins, WHERE and GROUP BY.
2. Never name a parameter after a SQL keyword.
3. Handle -2028 by recreating and retrying once.
4. Follow nextLink until all pages are read.
5. Reconcile totals with SAP once. Line totals ignore the document discount, which we explain in why SAP B1 sales reports don’t match.

Need a report or an integration built on SQLQueries? See our Service Layer guide or contact us.

Sources

• SAP Learning: Managing SQL Queries Using Service Layer API
• SAP Community: New SAP Business One Service Layer SQL Query
• SAP Community: Service Layer SQL Query 502 proxy error when retrieving
• SAP Community: Incorrect syntax near UNION (SQLQueries)

Related services

• SAP Business One development
• Web & B2B portals