How do I run a post-deployment smoke check on every environment?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A release has been deployed to QA and to production, and the team wants a clear yes or no for each stage. Running a handful of queries by hand on each one and reading the numbers by eye is slow, and a wrong figure is easy to miss.

You will write the checks once, as a Script that fails when a figure is not what the release expects, then run it on QA and on production and read one status line on each: SUCCESS when every check passed, FAILED with the line of the first check that did not. BroadSQL has no assertion command: you will use the documented behaviour of LET, which refuses a query that returns no row, together with ON ERROR STOP.

Short answer

Write each check as LET <name> = SELECT 'OK' FROM ... HAVING <condition>;, start the Script with ON ERROR STOP;, and run it with @<script> after ENV QA and after ENV PROD.

Before you start

This page uses the practice connections of Set up QA and production copies of WORLD, which do not exist by default: WORLD-QA for QA, and WORLD as the production database of the Database Group WORLDAPP. The Script only reads. Step 6 adds a test row to WORLD-QA to show a failure, and Clean up removes it.

1. Write the checks as questions with one expected answer

A smoke check asks a few questions whose answers the release fixes in advance. For WORLD:

• COUNTRY holds 254 rows and CITY 4081;
• the currency EUR exists;
• every region refers to an existing country: REGION.COUNTRY_CODE2 to COUNTRY.CODE2 is a declared foreign key, so it holds today, and a migration that broke it would show here.

2. Turn each check into a statement that can fail

A query that returns one row only when the check passes returns zero rows when it fails. For example, this query returns one row, OK, when COUNTRY holds 254 rows, and no row otherwise:

SELECT 'OK' FROM COUNTRY HAVING COUNT(*) = 254;

At the prompt, a query that returns no row is not an error. LET makes it one: it assigns the single value of a query to a variable, and refuses a query that returns no row with the message "The query returned no row, exactly 1 is required". In a Script that starts with ON ERROR STOP, that refusal ends the run with the status FAILED.

3. The Script

Save this text in the Scripts Library (the scripts folder of your installation), in a folder deploy:

-- @description: Post-deployment smoke check of WORLDAPP
-- @tags: world, deployment, check
-- @instance: WORLDAPP
ON ERROR STOP;
LET countries = SELECT COUNT(*) FROM COUNTRY;
LET cities = SELECT COUNT(*) FROM CITY;
ECHO 'COUNTRY rows: ${countries}, CITY rows: ${cities}';
LET check_countries = SELECT 'OK' FROM COUNTRY HAVING COUNT(*) = 254;
LET check_cities = SELECT 'OK' FROM CITY HAVING COUNT(*) = 4081;
LET check_euro = SELECT 'OK' FROM CURRENCY WHERE CODE = 'EUR';
LET check_regions = SELECT 'OK' FROM REGION R LEFT JOIN COUNTRY C ON C.CODE2 = R.COUNTRY_CODE2 HAVING COUNT(CASE WHEN C.CODE IS NULL THEN 1 END) = 0;
ECHO 'All checks passed';

What each line is for:

• @instance: WORLDAPP says which Database Group the Script is written for. Run from a connection of another group, BroadSQL prints a warning and runs it anyway: it is a reminder, not a guard.
• ON ERROR STOP ends the run at the first failed statement.
• The two counting LET lines keep the figures, and the first ECHO prints them, so a failure comes with the actual numbers. ECHO is never hidden by OUTPUT QUIET.
• Each check_ line is one assertion. The regions check counts the regions without a country and passes when there are none.
• The last ECHO is reached only when every check passed.

4. Run it on QA

Open the QA copy and run the Script by its path in the library:

CONNECT WORLD-QA;
@deploy/world-smoke.bsql;

The run ends with the two messages and the status line, which names the Script, says SUCCESS and counts the nine statements:

COUNTRY rows: 254, CITY rows: 4081
All checks passed
deploy/world-smoke.bsql: SUCCESS (9 statements, 0 failed) [run K7Q2M4XA]

The Run ID at the end identifies this run in the activity log.

5. Run it on production

ENV PROD opens the production connection of the same Database Group, here WORLD; run the Script there, then come back to QA:

ENV PROD;
@deploy/world-smoke.bsql;
ENV QA;

Every statement of the Script reads, so the Production confirmation never asks. / PROD would not do here: it runs the last SQL query once on production, not a Script.

6. See a failure

To see what a failed check looks like, make QA differ from what the release expects. This statement adds a test city to WORLD-QA only, your practice copy:

INSERT INTO CITY (NAME, COUNTRYCODE, DISTRICT, POPULATION) VALUES ('Testville', 'FRA', 'Test', 1000);

Run the Script on WORLD-QA again:

@deploy/world-smoke.bsql;

The first ECHO shows the actual figure, 4082 cities. The check_cities line then fails with "The query returned no row, exactly 1 is required", nothing after it runs, and the status line names the statement and the line of the Script where the run stopped:

COUNTRY rows: 254, CITY rows: 4082
ERROR: Script deploy/world-smoke.bsql stopped by ON ERROR STOP at statement 6 (line 9)
deploy/world-smoke.bsql: FAILED (6 statements, 1 failed) [run P3V8D1RJ], stopped at statement 6 (line 9)

Line 9 is the check_cities line: the line number tells you which check failed. Production is unaffected: after ENV PROD;, the same Script ends with SUCCESS.

7. Keep a record of each run

With IsLogActivated=TRUE in BroadSQL.ini, the activity log keeps the output of every run, with its status line and Run ID, in one file per connection and per day: a record of which checks ran where, and when. See Investigate a support ticket and keep the evidence.

Variations

• Expected values per release: declare them as parameters, -- @param expected_cities INTEGER = 4081, and write HAVING COUNT(*) = ${expected_cities}. Each release passes its own figures: @deploy/world-smoke.bsql expected_cities=4100;.
• A row that must not exist: LET no_test_rows = SELECT 'OK' FROM CITY WHERE NAME = 'Testville' HAVING COUNT(*) = 0;.
• The structure too: add the COMPARE TABLE STRUCTURE lines of Check that a deployment changed a table the same way in QA and production.
• Quieter runs: OUTPUT QUIET; after ON ERROR STOP; hides the routine lines; the ECHO messages and a failed status stay visible.

Watch out

• A check passes or fails only through a failed statement. A plain SELECT that shows a wrong figure does not fail the run: wrap every check in a LET.
• LET needs a query of exactly one column and exactly one row; a check that can return several rows fails for that reason, not because the check failed. HAVING without GROUP BY always returns one row or none.
• Keep the checks read only, so that a run on production never stops at a Production confirmation.
• ON ERROR STOP stops at the first failure: the checks after it are not run. Put the most important first.

Clean up

Remove the test city from the QA copy:

DELETE FROM CITY WHERE NAME = 'Testville';

The Script can stay in the library for the next release.

Related

• Output and error handling
• LET, ON ERROR, ECHO
• Check a figure in QA, then in production
• Write a report Script that stops at the first failure

Commands used

• LET
• ON ERROR
• ECHO
• @
• ENV

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