SQL Output Issues

This page collects the errors and unexpected results you can run into when BCCL writes data to SQL Server with -f sql. Each entry is keyed on the message or symptom you see on the console, explains where the problem originates (Business Central side or SQL side), and gives the diagnostic steps support uses.

How to Isolate BC-Side vs. SQL-Side Problems

A getdata run with -f sql involves two independent connections: the web service call to Business Central, and the SQL Server connection that receives the rows. Before you investigate a SQL error, confirm which side is failing.

Step 1: Run the Same Command with `-f json`

Run the identical command, but replace -f sql with -f json and leave out -o:

BCCL -t getdata -s table=<TableNo> -f json -m "<path>\<Name>.mapping.json"

This dumps the retrieved data to the console without requiring any SQL target table to exist.

If data is returned: the Business Central connection, the authentication (OAuth or S2S), the web service endpoint and the table= selection are all working. Any remaining error is on the SQL output side: the connection string, the target database, or the table named in -o.
If no data is returned or an error appears: the problem is on the Business Central side and has nothing to do with SQL. Continue in Common Issues or Authentication Issues.

Step 2: Re-Run with `-f sql` and `-o`

Once the JSON run returns rows, re-run with SQL output and an explicit target table:

BCCL -t getdata -s table=<TableNo> -f sql -o "<SqlTableName>" -m "<path>\<Name>.mapping.json"

Any error that appears now is a SQL-side error and is covered by the entries below.

`-o` Is Mandatory for SQL Output

When the output format is sql, the -o parameter has a different meaning than for file formats: it specifies the name of the SQL table that BCCL writes into. It is the SQL target table name, not a file name.

If you use -f sql but omit -o, BCCL does not write anything to the database. It retrieves the data, prints it to the console as JSON text on standard output, and exits without an error message.

Command	Result
`BCCL -t getdata -s table=<TableNo> -o "<SqlTableName>" -f sql -m "<path>\<Name>.mapping.json"`	Rows are written to the SQL table `<SqlTableName>`
`BCCL -t getdata -s table=<TableNo> -f sql -m "<path>\<Name>.mapping.json"`	Nothing is written to SQL; the data is only printed to the screen

The symptom "no error, but no rows in SQL, and JSON printed in the console" is therefore the normal result of a missing -o.

Note: The mapping file (-m) alone does not determine the SQL target table name. The top-level key in the mapping file is informational. You must always supply -o for SQL output.

Empty Result Set

Output of this form means that the source table in Business Central returned no records:

{"<Table Name>":[]}

There is nothing for BCCL to write to SQL. This is not an error, and it is not a mapping or SQL problem. Check the table number and the view filter you supplied.

When you see an empty array printed to the screen while using -f sql, two things can be true at the same time:

-o is missing, so the data was printed instead of written (see above).
The source table is empty or the filter excludes every record.

Check both before changing anything else.

"Unknown or wrong SQL table \"

Error text:

Unknown or wrong SQL table <name>

Cause: BCCL was asked (via -o <name> together with -f sql) to write to a SQL table that it cannot find in the configured target database.

The error originates on the SQL side, not on the Business Central side. It does not indicate a problem with the BC connection, authentication or the web service endpoint. If you have not already done so, confirm this with the -f json test described above.

Checks, in order:

Verify that the table exists. The table named in -o must exist in the target database, with columns that match the MappedName values in the mapping file.
Verify the spelling. Include the schema prefix where relevant (for example dbo.<Table>), and check names containing spaces against names without spaces. [Company$Location] and Company$Location are not interchangeable if the table was created with a different name.
Verify which SQL Server and database this machine points to. The connection string in --sqlconnect, or the remembered SqlConnection value on this machine, decides which database is used. The same mapping file and command can work from another machine simply because that machine's SQL connection points at a database where the table exists.

"Cannot update table ... Incorrect syntax near '='"

Symptom: a command such as

BCCL -t getdata -s table=10 -f sql -m "<mapping>.map.json" -o "bc.[<TableName>]"

fails with

Cannot update table bc.[<TableName>] in SQL, error: Incorrect syntax near '='.

Incorrect syntax near ','.

and the run ends with

Sql table bc.[<TableName>], 0 inserted, 0 updated

Where the error comes from: the message text is produced by SQL Server itself. In verbose output the error block shows Source: Core .Net SqlClient Data Provider. This means the statement that BCCL generated was rejected by SQL Server. It is not a Business Central problem and not a license problem.

Likely cause to check first: a field name or bracket (column naming) problem in the mapping file or in the -o target table specification. Illegal or unquoted column names produce exactly these Incorrect syntax near '=' and Incorrect syntax near ',' messages.

Diagnostic procedure:

Re-run the same command with -v to get the request JSON (including the complete Mapping array) and the per-row parameter list.
Make sure you are running BCCL EXE 3.23.0.374 or newer. From build 3.23.0.374, verbose mode prints the actual generated SQL statement in the error message, which is what identifies the offending column or field. Earlier builds (for example 3.23.0.373) show only the SQL Server message and the parameter list, not the statement.
If needed, download the newer EXE from the on-premises download area (see Client Versions and Updates). Only the executable needs to be replaced; the BC app version can stay unchanged.
Re-run with -v and inspect the printed SQL statement around the reported LineNumber to find the field or column name causing the syntax error. The Mapping array in the request JSON shows exactly which BC field is mapped to which SQL column name.

See Diagnosing with Verbose Mode for how to read the verbose output.

"String or binary data would be truncated"

Symptom: the run reports

Message: String or binary data would be truncated.
The statement has been terminated.
Source: Core .Net SqlClient Data Provider

Rows that trigger this error are not written (the statement is terminated). Other rows may still be inserted or updated, as shown by the closing summary line for the table:

Sql table [<Company>$<Table>], <n> inserted, <n> updated

Cause: a value coming from Business Central is longer than the target column in the SQL table.

Diagnosis procedure:

Re-run the same command adding -v.
In the verbose output, locate the error block. Immediately after it, BCCL prints the full parameter list of the offending row, with a Size value per parameter:

   Parameter <ColumnName><n> = <value> (NVarChar Size <n>)

Compare each Parameter <name> = <value> (NVarChar Size n) against the defined width of the matching column in the SQL target table. The Size value is the actual character length of the value being sent, so the parameter whose size exceeds the column width is the one causing the error.
Remedy by widening the target SQL column, or by ensuring that the source data fits.

About --sqlprecommand "SET ANSI_WARNINGS OFF": you can try setting this session option on the connection (see the reference below). Be aware that this has been reported not to suppress this particular error in practice. Before drawing any conclusion, verify with -v that the PreCommand is actually processed; the verbose output tells you whether it ran.

Can the value be shortened in the mapping file? No. A mapping entry consists of FieldNo, FieldName, MappedName, Validate and, for fields with a table relation, CreateRelatedStub. It is a field-to-column assignment and contains no expression or length property, so something like Left(Description,30) is not supported. For length mismatches, follow the procedure above and adjust the target column or the source data.

"System.OverflowException: Value was either too large or too small for an Int32"

Symptom: BCCL aborts with an unhandled exception during a getdata run with SQL output. The stack trace contains lines such as:

Unhandled exception. System.OverflowException: Value was either too large or too small for an Int32.
   at System.Convert.ToInt32(Int64 value)
   at Newtonsoft.Json.Linq.JToken.op_Explicit(JToken value)
   at BCCL.Program.GetValue(JToken record, String column)
   at BCCL.Program.ImportIntoSql(...)

A typical command shape that triggers it:

BCCL -t getdata -s table=36 -o Sales_Header -m Sales_Header.mapping.json

Cause: the table contains BigInteger (Int64) fields whose values exceed the Int32 range. In BCCL 2.x clients these fields can be handled internally as Int32, so BCCL tries to convert a 64-bit value into a 32-bit integer and fails.

This is known to happen with fields added by the Shopify Connector, which introduces BigInteger values on standard tables such as Sales Header (table 36), and on the Shopify tables themselves, for example table 30141 Shpfy Return Line and table 30142 Shpfy Refund Header.

Things that do not fix it:

It can surface after a Business Central version upgrade (reported on BC 26.1) even though the SQL table was recreated and the mapping file regenerated. Recreating the table and regenerating the mapping does not resolve it.
Having the SQL columns correctly declared as bigint does not by itself resolve the overflow in older clients. This was confirmed in a real case where the columns were already bigint and the error still occurred.

Solutions, in the order support gives them:

Preferred: upgrade to the BCCL 3.0 client executable. This resolves the problem; after switching to 3.0 the same export completes successfully. If a suitable release is not yet on the download page, the beta client is available from https://www.hougaard.com/on-premises/BCCL/beta/. The 3.x client is backward compatible with the 2.x BC extension, so you can swap in the client on its own without upgrading the extension first. See Release Notes.
Workaround if you cannot upgrade and do not need the fields: open the mapping file and remove the mapping entries for the offending BigInteger fields (for example the Shopify-added fields on the table). Only fields present in the mapping file are exported, so removing them avoids the conversion.
There is no way to "correct" the mapping so that the value converts. Manual mapping edits can only exclude the fields; the actual fix is the 3.0 client.

Before troubleshooting further, run BCCL.EXE with no arguments to confirm which client version you are on.

Are some tables blocked? No category of Business Central tables is blocked from BCCL. Shopify tables and other standard-app tables are readable like any other table. An error on such a table indicates a data-type or client-version problem, not an access restriction. (The only exception is a small set of platform-restricted system tables that Business Central itself refuses to open; see Common Issues.)

Reference: `--sqlprecommand`

--sqlprecommand takes a SQL statement that BCCL executes on the SQL connection before it starts working with the data.

Typical uses:

Setting session options on the connection, for example SET ANSI_WARNINGS OFF.
Truncating a staging table before a full reload, for example TRUNCATE TABLE dbo.Customer.

Example:

BCCL -t getdata -s table=18 -f sql -o Customer --sqlconnect "Server=<server>;Database=<db>;User id=<user>;Password=<password>" --sqlprecommand "SET ANSI_WARNINGS OFF"

Remembered parameters: when you use --remember, the value is persisted in the remembered-parameters JSON under the key SqlPreCommand. A sanitised remembered block looks like this:

{
  "UseOAuth": false,
  "BC_URL": "<your BC url>",
  "Format": "sql",
  "Auth": "S2S",
  "ClientId": "<client id>",
  "ClientSecret": "<secret>",
  "TenantId": "<tenant id>",
  "SqlConnection": "server=<server>;Database=<db>;User id=<user>;Password=<password>",
  "SqlPreCommand": "SET ANSI_WARNINGS OFF"
}

Verifying that the pre-command runs: run the same command with -v. The verbose output tells you whether the PreCommand is processed (and, when it is, the result of executing it). Always check this before assuming that a session option had no effect.

Quick Reference

Symptom	Side	First action
JSON printed to console, nothing in SQL, no error	Command line	Add `-o "<SqlTableName>"`
`{"<Table Name>":[]}`	Business Central	Check table number and `view` filter; also check `-o`
`Unknown or wrong SQL table <name>`	SQL	Verify table exists, spelling/schema, and which database this machine uses
`Incorrect syntax near '='` / `near ','`	SQL	Re-run with `-v` on EXE 3.23.0.374 or newer; inspect generated statement
`String or binary data would be truncated`	SQL	Re-run with `-v`; compare parameter `Size` to column widths
`System.OverflowException ... Int32`	Client version	Upgrade to the 3.0 client; or exclude BigInteger fields

SQL Output Issues

How to Isolate BC-Side vs. SQL-Side Problems

Step 1: Run the Same Command with -f json

Step 2: Re-Run with -f sql and -o

-o Is Mandatory for SQL Output