FogBugz FYS 8.8.55: Resolving SQL “Incorrect syntax near 'JSON'” and Creating a Searchable Archive for Decommissioning

Solution

Scenario

You want to decommission an on-prem FogBugz FYS 8.8.55 instance but keep historical cases searchable (primarily case text + comments + attachment filenames/metadata). REST API extraction (for example, Python + REST API exports) may be too slow or unreliable for bulk archival.

How to recognize the SQL Server JSON export issue

When running JSON export queries in SQL Server, you may receive errors such as:

• [S0001][102] Line X: Incorrect syntax near 'WITHOUT_ARRAY_WRAPPER'.
• [S0001][102] Line Y: Incorrect syntax near 'JSON'.

Root cause

SQL Server native JSON features (FOR JSON PATH, JSON_QUERY, WITHOUT_ARRAY_WRAPPER) are available starting SQL Server 2016. If your FogBugz database is hosted on an older SQL Server version, JSON export queries will fail with syntax errors. This is a SQL Server version/feature limitation rather than a FogBugz-specific JSON export feature.

1) Create an authoritative archive: full SQL Server backup (.bak)

This preserves everything in one artifact you can restore later (including attachments stored in the database).

SSMS steps

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server hosting the FogBugz database.
2. Locate the FogBugz database (name varies by installation).
3. Right-click the database → Tasks → Back Up…
4. Backup type: Full
5. Destination: Disk → choose a path with sufficient free space.
6. Run the backup and confirm it completes successfully.
7. Strongly recommended: do a test restore to another SQL Server instance before decommissioning.

T-SQL template

BACKUP DATABASE [<FOGBUGZ_DB_NAME>]
TO DISK = 'D:\Backups\<FOGBUGZ_DB_NAME>_FULL.bak'
WITH COPY_ONLY, COMPRESSION, INIT, STATS = 10;

2) Export JSON in bulk (fast) by restoring the .bak to SQL Server 2016+

If your production SQL Server is older than 2016, avoid changing it. Instead:

1. Take the .bak backup (Step 1).
2. Restore it onto a separate machine/VM running SQL Server 2016+ (can be temporary).
3. Run bulk JSON export queries against the restored copy.
4. Load the resulting “one document per case” JSON into Elasticsearch/OpenSearch.

This avoids REST overhead and avoids modifying a fragile/end-of-life production environment beyond creating the backup.

3) If you cannot use SQL Server 2016+: export relational rows and assemble JSON externally

If restoring to SQL Server 2016+ isn’t possible:

• Export core tables (commonly Bug, BugEvent, Attachment metadata) to CSV/TSV.
• Assemble nested JSON outside SQL Server (ETL/script) to create one document per case (events/comments as a nested array; attachments metadata as a nested array).
• Alternative: use FOR XML on older SQL Server and convert XML → JSON afterward.

4) If the FogBugz schema viewer URL returns 404, validate schema directly in SQL Server

If https://<your_fogbugz_url>/f/page?pg=pgSchema returns a 404, the schema viewer path may vary by deployment/version or be disabled. Validate schema directly via SQL Server system catalog queries instead.

Confirm key tables exist

SELECT name
FROM sys.tables
WHERE name IN ('Bug','BugEvent','Attachment')
ORDER BY name;

Inspect columns for a table

SELECT c.name, t.name AS type_name
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.<TABLE_NAME>')
ORDER BY c.column_id;

5) Installer access (FogBugz FYS 8.8.55)

FogBugz FYS installer downloads may be license-gated (no public download link). If you need the installer:

• Provide proof of entitlement (for example, an order/license reference like <order_id>).
• Retrieve the installer via an authenticated transfer method (commonly SFTP) using a client such as FileZilla:
  • Host: sftp://<sftp_host>
  • Username: <sftp_username>
  • Password: <sftp_password>
• Note: SFTP cannot be accessed in a web browser; credentials are typically time-bound and should be treated as secrets.

Related Symptoms

Email reply bounce message

If replies made by email bounce with:

“Replies made to this mailbox are routed to an unmonitored mailbox. Please do not reply to this message.”

This indicates the reply address routes to a mailbox that is not monitored for inbound processing. If you need email-based updates, ensure your FogBugz mail handling is configured to use a monitored mailbox; otherwise, use the FogBugz web UI for case updates.

Windows 11 / IIS 10 installer detection issues

During installation, you may encounter an error where IIS 10.0 is not recognized as installed by the installer. If this occurs, verify IIS is installed and enabled (including any required components) and consider installing on a supported server environment if possible.

Support Scope & Product Constraints

• FogBugz FYS is end-of-life.
• Database migration/conversion efforts (including export/ETL conversion work) are outside standard support scope.
• FogBugz FYS does not include a built-in mechanism to convert an existing MS SQL Server–backed deployment to MySQL. Any MS SQL Server → MySQL conversion would be a custom, customer-owned migration (schema mapping, transformation, validation).
• For decommissioning plus a searchable archive, the recommended low-risk approach is to keep an authoritative SQL .bak and restore it to SQL Server 2016+ for bulk export, rather than changing database backends.

Verification Checklist

• The .bak backup completes successfully and can be restored on a separate SQL Server instance.
• On SQL Server 2016+, a test FOR JSON query runs without syntax errors.
• The exported JSON includes:
  • Case fields (from Bug)
  • Event/comment history (from BugEvent)
  • Attachment metadata (for example, filename, IDs; excluding binary/blob fields to keep exports manageable)
• Sample documents load into Elasticsearch/OpenSearch and are searchable by case title/description/comments and attachment filenames.