Reading Prophet 21 data with SQL, safely

Prophet 21

How-toIntermediate5 min read

In short. Use a dedicated read-only login, understand what your queries do to locking on a busy system, switch to the API when you need to write and filter out deleted and other-company rows.

Written for Report writers, administrators, developers.

Direct SQL is the fastest way to answer questions about Prophet 21 data, and the fastest way to cause an incident if it is done carelessly. The risks are not exotic. Someone runs an update they meant to be a select, or a heavy report query holds locks while the order desk is trying to save. The setup and habits below make reading P21 data routine.

Everything here is about reading. Changing Prophet 21 data should go through the application, its import tools or its APIs, so that business logic and related tables stay consistent.

Do not query with an administrator account, a shared application account or the account Prophet 21 itself uses. Create a login that can only read, and use it for reporting tools, analysts and ad hoc queries.

A minimal version, run by a database administrator:

CREATE LOGIN p21_reader WITH PASSWORD = 'use-a-generated-secret-here';

USE [YourP21Database];
CREATE USER p21_reader FOR LOGIN p21_reader;
ALTER ROLE db_datareader ADD MEMBER p21_reader;

Notes on doing this well:

Prefer Windows or directory authentication where your environment supports it, so the password stays out of connection strings.
Create one login per purpose. A reporting tool, a data warehouse job and a person each get their own. When something misbehaves, you can see which one it was.
Remember that db_datareader reads every table. That includes sensitive data such as payment or payroll-adjacent tables if your install has them. If that is too broad, grant SELECT on specific tables or on views you create for reporting.
Store the credential in a secrets manager, never in a spreadsheet or a report file on a shared drive.

A read-only login removes the worst outcome, an accidental write. It does not remove the second-worst outcome, which is a read that slows everyone down.

Understand what your queries do to locking

SQL Server's default isolation level, read committed, takes short-lived shared locks as a query reads rows. On a quiet database you never notice. On a busy ERP, a long-running query that scans a large table can block writers, and writers can block your query. To users, that looks like order entry hanging.

You have a few tools, each with a trade-off:

Approach	What it does	Trade-off
Keep queries small and selective	Less data read, locks held briefly	Requires filtering on indexed columns
Row versioning (read committed snapshot or snapshot isolation)	Readers see a consistent snapshot without blocking writers	A database-level setting that uses tempdb. A DBA decision, made once for the database
`NOLOCK` or read uncommitted	Readers take no shared locks	Can return uncommitted rows, skip rows or count rows twice while data moves
A reporting copy	Queries run somewhere else entirely	Data is only as fresh as the last copy

Our guidance:

For ad hoc questions, write selective queries and run them outside peak hours when they are large.
For recurring reports and dashboards, point them at a reporting copy if you have one (a restored backup, a replica or a warehouse).
Treat NOLOCK as a known compromise, not a performance switch. It is acceptable for a rough count on a live system. It is not acceptable for anything financial.
Do not change database-level isolation settings on a production ERP without testing and without your vendor's guidance.

You can check which row-versioning options a database has enabled with a harmless query:

SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

Habits that keep queries safe

These habits apply to every query you run against P21, ad hoc or scheduled.

Habit	Why
Always filter out logically deleted rows	Many P21 tables use a flag to mark deleted records rather than removing them
Join on the company as well as the key	Multi-company databases share tables
Select columns, not `*`	Wide tables are expensive to read in full
Put a `TOP` on exploratory queries	Until you know how many rows come back
Handle flags defensively	Yes or no columns hold `'Y'` or `'N'`, but a `NULL` can appear. Compare in a way that treats `NULL` deliberately
Never leave a transaction open in your query tool	An uncommitted `BEGIN TRAN` holds locks until you close the window

Starter queries

These are short, original examples on well-known tables. Column names and flag values can vary between versions, so check them in your own database before relying on the results.

Open orders for one customer

DECLARE @customer_id decimal(19, 0) = 100123;

SELECT TOP (200)
       h.order_no,
       h.order_date,
       h.po_no,
       c.customer_name
FROM dbo.oe_hdr AS h
JOIN dbo.customer AS c
  ON c.customer_id = h.customer_id
 AND c.company_id = h.company_id
WHERE h.customer_id = @customer_id
  AND COALESCE(h.delete_flag, 'N') <> 'Y'
  AND COALESCE(h.completed, 'N') <> 'Y'
  AND COALESCE(h.cancel_flag, 'N') <> 'Y'
ORDER BY h.order_date DESC;

Lines on an order with what is still open

DECLARE @order_no varchar(8) = '1000123';

SELECT l.line_no,
       m.item_id,
       m.item_desc,
       l.qty_ordered,
       l.qty_invoiced,
       l.qty_canceled,
       l.qty_ordered - l.qty_invoiced - l.qty_canceled AS qty_open,
       l.unit_price
FROM dbo.oe_line AS l
JOIN dbo.inv_mast AS m
  ON m.inv_mast_uid = l.inv_mast_uid
WHERE l.order_no = @order_no
  AND COALESCE(l.delete_flag, 'N') <> 'Y'
ORDER BY l.line_no;

Stock for an item across locations

DECLARE @item_id varchar(40) = 'ABC-123';

SELECT il.location_id,
       il.qty_on_hand,
       il.qty_allocated,
       il.qty_on_hand - il.qty_allocated AS qty_free
FROM dbo.inv_mast AS m
JOIN dbo.inv_loc AS il
  ON il.inv_mast_uid = m.inv_mast_uid
WHERE m.item_id = @item_id
  AND COALESCE(m.delete_flag, 'N') <> 'Y'
ORDER BY il.location_id;

When SQL is the wrong tool

For some needs, a different path is safer than a query.

Need	Use instead
To change data	The application, an import or the API
Real-time integration	Polling tables for changes is fragile and puts constant load on the database. See Prophet 21 integration options
Numbers finance will sign off	The report finance already trusts, or reconcile your query to it first

A good pattern is to keep a small library of reviewed queries in version control, each with a short note on what it answers and which version of P21 it was checked against. New questions start from a query that is known to be right.