How do I write a report Script that stops at the first failure?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

You start a Script that refreshes data and writes the weekly report, then turn to something else. Ten minutes later you find it waiting for an answer at step two, or worse, it carried on after a failed step and wrote a report from incomplete data. A report built from a run that failed halfway is worse than no report.

You will prepare a Script so that no step waits for an answer, the run stops at the first failed step, and the screen shows only what matters while the activity log keeps everything. Then you will make the report itself depend on the run: DUMP LIB writes the report file only when the whole Script ended with SUCCESS. You will read the status line of each run, see a run stop, and know what changes when BroadSQL has no interactive terminal to ask questions on.

Short answer

Start the Script with ON ERROR STOP;, use LOAD ... EXECUTE instead of plain LOAD, keep changes to Production connections out of it, and write the report with DUMP LIB <script> TO <file> AS XLSX;, which writes nothing unless the run ends with SUCCESS.

What can stop a Script, and how to prepare for it

Three things interrupt a run. The table says what happens at the prompt and in a Script, and what to write instead:

1. A refresh Script that stops at the first failure

This Script refreshes a local copy of country figures by key and adds this week's snapshot to a workbook. It only reads WORLD; it writes the local database WORKCOPY and the file world_report.xlsx in the export folder. Save it in the Scripts Library (the scripts folder):

-- @description: Refresh the local country figures and add this week's snapshot to the report
-- @tags: world, report, weekly
ON ERROR STOP;
OUTPUT QUIET;
DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO WORKCOPY.COUNTRY_FIGURES AS H2 MODE MERGE KEY(CODE);
DUMP (SELECT CURRENT_DATE AS SNAPSHOT_DATE, TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES FROM COUNTRY GROUP BY CONTINENT) TO world_report.HISTORY AS XLSX MODE APPEND;
OUTPUT NORMAL;
ECHO 'Weekly refresh done';

What each line is for:

• ON ERROR STOP ends the run at the first failure: if the merge fails, no snapshot is added to the report.
• OUTPUT QUIET hides the routine messages of the two exports on screen; the activity log still records them.
• OUTPUT NORMAL shows output again, and ECHO prints a closing message, which OUTPUT QUIET would not hide anyway.

2. Run it and read its status

Connected to WORLD, run it:

@world/weekly-refresh.bsql;

The screen shows the closing message, then one status line: the name of the Script, its status (SUCCESS when every statement succeeded), the number of statements and failures, and a Run ID that identifies this run in the activity log. Run it again next week: the merge updates only what changed, and the workbook gets seven new rows.

3. See it stop

Open world_report.xlsx in Excel, then run the Script again. The second DUMP cannot replace a file that Excel holds open, so it fails. ON ERROR STOP ends the run there, the ECHO does not run, and the status line says FAILED, with the number and the line of the statement where the run stopped. Close the workbook and run the Script again.

4. A report written only when the run succeeds

The refresh above writes as it goes. A report file should rather be written once, at the end, and only when everything before it went right. DUMP LIB does exactly that: it runs a Script of the library without displaying its results, and writes the Script's final result to a file only when the run ends with the status SUCCESS.

Save a second Script whose last statement is the report, after a check that the data is complete. The check is a LET that fails when COUNTRY does not hold the 254 rows the report expects, as in Run a post-deployment smoke check:

-- @description: Continent summary of the weekly report, only from complete figures
-- @tags: world, report, weekly
ON ERROR STOP;
LET check_countries = SELECT 'OK' FROM COUNTRY HAVING COUNT(*) = 254;
SELECT CURRENT_DATE AS REPORT_DATE, TRIM(CONTINENT) AS CONTINENT, COUNT(*) AS COUNTRIES, SUM(POPULATION) AS POPULATION
  FROM COUNTRY GROUP BY CONTINENT ORDER BY POPULATION DESC;

Export its result to the tab SUMMARY of a report file:

DUMP LIB world/continent-report.bsql TO continent_report.SUMMARY AS XLSX;

The run succeeds, and continent_report.xlsx is written in the export folder with seven rows in SUMMARY. The LET query is never the exported result: only the final query is.

5. See the report refused

Make the check fail without touching any data: edit continent-report.bsql and change 254 into 255. Then run the same export again:

DUMP LIB world/continent-report.bsql TO continent_report.SUMMARY AS XLSX;

The LET fails with "The query returned no row, exactly 1 is required", ON ERROR STOP ends the run, and the Script does not end with SUCCESS, so DUMP LIB writes nothing: the SUMMARY tab written in step 4 is left as it was. Change 255 back into 254.

6. A tab that grows week after week

The same export with MODE APPEND adds each week's rows below the previous ones, and the REPORT_DATE column says which week each block belongs to:

DUMP LIB world/continent-report.bsql TO continent_report.GROWTH AS XLSX MODE APPEND;

Run once a week, GROWTH becomes the history of the figures, and a week whose check failed adds no rows.

7. Keep a record of each run

Set IsLogActivated=TRUE in BroadSQL.ini (it is TRUE in the Linux broadsqlux.ini): every command and its output is written to one file per connection and per day, including the lines OUTPUT QUIET hid, the status line and the Run ID. Search the log of the day for the Run ID of step 2 to find that run. See Investigate a support ticket and keep the evidence.

Without an interactive terminal

What the documentation of this release establishes when BroadSQL cannot ask a question, for example when its input is redirected from a file:

• Every statement that can change a database of a Production Environment is refused, with no setting to turn this off. The message ends with "confirmation is required and no interactive confirmation is available. Nothing was executed."
• LOAD in a Script never asks anyway: with EXECUTE it writes, without it it refuses.

Starting BroadSQL from a scheduler (Windows Task Scheduler, cron) is not described in the documentation of this release, and BroadSQL asks for the master password when it starts. This page therefore does not describe unattended runs: prepare the Script as above and start it yourself.

Variations

• One command for the whole report: a parent Script that calls @world/weekly-refresh.bsql;, then holds the final query. A called Script starts with the caller's ON ERROR setting, and a called Script that ends FAILED fails the parent's statement, so DUMP LIB of the parent writes nothing when either part fails.
• A CSV report: DUMP LIB world/continent-report.bsql TO continent_report AS CSV;.
• Arguments: DUMP LIB passes named arguments to the Script, as @ does: see Save a query with parameters and run it by name.

Watch out

• Plan changes to Production databases for an interactive session. A Script that writes to Production asks at each change (or once per session, with ProductionConfirmEveryChange=FALSE), and a refusal stops the whole run.
• ON ERROR STOP stops before the next statement; what already ran stays done. With Autocommit off, a later COMMIT in the Script is not reached.
• LOAD ... EXECUTE validates the file again; it does not wait for anyone to read a preview.
• A REPEAT without FOR or COUNT runs until Ctrl+C: give it one in a Script.
• Close the report workbook before every run: an open file makes the export fail.

Clean up

Delete world_report.xlsx and continent_report.xlsx from the export folder; for WORKCOPY, DEL CONNECTION WORKCOPY;, then delete WORKCOPY.mv.db while BroadSQL is closed. The Scripts can stay in the library.

Related

• Output and error handling
• Exporting Script results
• Build a weekly Excel report
• Production confirmation
• Using LOAD in scripts

Commands used

• ON ERROR
• OUTPUT
• ECHO
• LET
• DUMP
• LOAD
• @

Found something wrong or unclear on this page? Tell us, with the page address.