Troubleshooting Stonefield Query in Sage 300 ERP: Common Issues & Fixes

Troubleshooting Stonefield Query in Sage 300 ERP: Common Issues & Fixes

Stonefield Query is a powerful reporting add-on for Sage 300 ERP, but users can encounter configuration, performance, and data issues. This guide lists common problems, diagnostics, and step-by-step fixes so you can restore reporting quickly.

1. Installation and Licensing Problems

• Issue: Stonefield Query won’t start or shows license errors.
  • Fix:
    1. Verify license file: Ensure the license (.lic) is present in the Stonefield Query program folder on the server.
    2. Run as admin: Launch Stonefield Query with administrator rights to allow registry or file updates.
    3. Check module compatibility: Confirm your Stonefield Query version is compatible with your Sage 300 ERP version (use vendor documentation).
    4. Re-register COM components: If errors reference COM, run a repair from the installer or re-register DLLs using regsvr32 as directed by Stonefield support.

2. Connection and Data Access Errors

• Issue: Cannot connect to Sage 300 company or database; missing tables/fields.
  • Fix:
    1. Confirm ODBC/Database connection: Test the Sage 300 database connection from the server using ODBC Administrator or SQL Client; correct DSN settings, credentials, and network access.
    2. Check Sage 300 service credentials: Ensure the service account has read access to the database and necessary Sage 300 company folders.
    3. Verify company code and database mapping: In Stonefield Query data source settings, confirm you selected the correct company database and mappings.
    4. Load tables metadata: Use the “Refresh” or “Load Fields” option in Stonefield Query to update its metadata if fields were recently added or changed.

3. Missing or Incorrect Report Data

• Issue: Reports show incomplete, duplicated, or incorrect records.
  • Fix:
    1. Validate joins and keys: Open the query and confirm table joins use the correct primary/foreign keys; incorrect joins cause duplication or missing rows.
    2. Check filters and criteria: Remove or simplify filters to see raw data, then reapply carefully. Ensure date ranges, company codes, and status flags are correct.
    3. Inspect data in source tables: Run the equivalent SQL directly against the database to confirm source data integrity.
    4. Use DISTINCT or grouping: If duplicates are expected due to joins, apply DISTINCT or aggregate/grouping to remove unintended duplicates.

4. Performance and Slow Reports

• Issue: Queries or reports run slowly or time out.
  • Fix:
    1. Optimize joins: Restrict joins to only required tables and ensure joins use indexed columns.
    2. Limit returned rows: Apply tighter filters, date ranges, or pagination.
    3. Use server-side SQL when possible: Convert complex client-side calculations into optimized SQL views or stored procedures on the database server.
    4. Update statistics and indexes: Coordinate with DBAs to rebuild indexes and update statistics on large tables.
    5. Run during off-peak hours: Schedule heavy reports during low-usage windows.

5. Formatting and Export Issues

• Issue: Exported reports (Excel, PDF) lose formatting or show errors.
  • Fix:
    1. Check export options: Use Stonefield Query’s export settings to set appropriate formats and encoding.
    2. Reduce large datasets: Export summaries instead of raw transactional detail to avoid spreadsheet limits.
    3. Update Office/PDF drivers: Ensure Excel and PDF drivers on the workstation/server are current and compatible.
    4. Test alternative formats: If Excel export fails, try CSV or PDF to isolate format-specific issues.

6. Security and Permission Problems

• Issue: Users cannot access certain queries or data.
  • Fix:
    1. Review Stonefield Query user roles: Confirm user permissions within Stonefield Query are set to allow viewing/running the needed queries.
    2. Check Sage 300 security settings: Ensure the user’s Sage 300 role grants access to the underlying modules and companies.
    3. Network file permissions: If queries or templates are stored on a shared folder, verify NTFS/share permissions permit read/write as required.

7. Script, Formula, or Calculation Errors

• Issue: Calculated fields return errors or unexpected values.
  • Fix:
    1. Validate expressions: Check syntax and data types in calculated fields; convert data types explicitly where needed.
    2. Test intermediate steps: Break complex formulas into simpler calculated fields to isolate the faulty portion.
    3. Handle nulls: Add null checks or default values to avoid runtime errors from missing data.

8. Compatibility After Upgrades

• Issue: Queries break after upgrading Sage 300 or Stonefield Query.
  • Fix:
    1. Review release notes: Check both products’ compatibility matrices before upgrades.
    2. Re-export/import queries: Some upgrades require reloading query definitions or refreshing metadata.
    3. Patch to latest build: Install vendor patches that address known compatibility issues.

When to Escalate to Support

• Persistent licensing, COM registration, database corruption, or unexplained crashes after following the above steps should be escalated. Collect these items before contacting support:
• Environment details: Sage 300 and Stonefield Query versions, OS, database type/version, and recent changes.
• Reproduction steps: Exact query/report and steps to reproduce the issue.
• Log files and screenshots: Error messages, logs, and example export files.

Quick Checklist (useful when troubleshooting)

1. Confirm versions and compatibility.
2. Test database/ODBC connectivity.
3. Refresh table/field metadata.
4. Validate joins, filters, and calculated fields.
5. Optimize queries and indexes for performance.
6. Verify user permissions and file shares.
7. Reproduce & collect logs before escalating.

If you want, I can craft a step-by-step troubleshooting checklist tailored to your environment (Sage 300 version, database type, and Stonefield Query version).