Microsoft DynamicsFree referencePDF, Excel, Word exportMicrosoft Dynamics GPAll Microsoft Dynamics GP tools

Dynamics GP System Tables Reference: SY00500, SY01400, SY03900 and the DYNAMICS Database

This reference covers the Dynamics GP system tables most module table maps skip: SY tables in each company database, the user, company and security tables in the DYNAMICS system database, and the DEX_LOCK and DEX_SESSION lock tables in tempdb. Each row gives the key columns and how it joins.

Download:

Table	Database	Display name	Key columns	Use it for	Join tip
SY00500	Company	Posting Definitions Master (batch headers)	BCHSOURC, BACHNUMB	Find stuck or marked batches: BCHSTTUS (batch status), MKDTOPST (marked to post), USERID	Match SY00800 on BACHNUMB and BCHSOURC plus company name
SY00800	DYNAMICS	Batch Activity	BACHNUMB, BCHSOURC, USERID, CMPNYNAM	Batches currently open or being posted by a user	Stale rows here keep a batch locked after a crash
SY00801	DYNAMICS	Resource Activity	USERID, CMPNYNAM	Resources (such as posting processes) locked by a user	Clear only for users with no ACTIVITY row
ACTIVITY	DYNAMICS	User Activity (logged-in users)	USERID, CMPNYNAM, LOGINDAT, LOGINTIM, SQLSESID	Who is logged in to which company	SQLSESID links to DEX_SESSION.session_id in tempdb
DEX_SESSION	tempdb	Dexterity sessions	session_id, sqlsvr_spid	Map a GP session to its SQL Server SPID	Orphans remain when users are killed; clean with ACTIVITY
DEX_LOCK	tempdb	Dexterity record locks	session_id, row_id, table_path_name	Find which session holds a record lock (for example a document in use)	Join session_id to DEX_SESSION, then to ACTIVITY.SQLSESID
SY01400	DYNAMICS	Users Master	USERID, USERNAME	List GP users and their names	Join SY60100 on USERID for company access
SY01402	DYNAMICS	User Defaults	USERID, syDefaultType, SYUSERDFSTR	Per-user saved settings and defaults	One row per user per default type
SY01500	DYNAMICS	Company Master	CMPANYID, INTERID, CMPNYNAM	Map company ID to database name (INTERID) and display name; next note index (NOTEINDX)	INTERID = the company database name
SY60100	DYNAMICS	User-Company Access	USERID, CMPANYID	Which users can open which companies	Join SY01500 on CMPANYID for names
SY10500	DYNAMICS	Security Assignment User Role	USERID, CMPANYID, SECURITYROLEID	Roles assigned to a user per company	Join SY10600 on SECURITYROLEID
SY10600	DYNAMICS	Security Assignment Role Task	SECURITYROLEID, SECURITYTASKID	Tasks inside each role	Join SY10700 on SECURITYTASKID
SY10700	DYNAMICS	Security Assignment Task Operations	SECURITYTASKID, DICTID, SECRESTYPE, SECURITYID	Windows, reports and tables each task grants	Resolve SECURITYID with the Security Resource Descriptions
SY09000	DYNAMICS	Security Tasks Master	SECURITYTASKID	Task names and descriptions	Join from SY10600
SY09100	DYNAMICS	Security Roles Master	SECURITYROLEID	Role names and descriptions	Join from SY10500
MC40200	DYNAMICS	Currency Setup	CURNCYID, CURRNIDX	Currency IDs, descriptions and index	CURRNIDX is the key used in many transaction tables
DU000020	DYNAMICS	Product versions per company	companyID, PRODID, versionMajor, versionMinor, versionBuild	Check each company is at the same GP and add-in version before an upgrade	companyID matches SY01500.CMPANYID
DB_Upgrade	DYNAMICS	Database upgrade status	db_name, PRODID, db_verMajor, db_verMinor, db_verBuild, db_status	Find a database or product stuck mid-upgrade	db_status not 0 means an incomplete upgrade
SY00100	Company	System Database Setup	DBNAME	Name of the system database the company belongs to	Must keep the system database name after a test restore
SY03900	Company	Record Notes Master	NOTEINDX	Note text (TXTFIELD) attached to records	Join on the NOTEINDX column of the master record, for example RM00101.NOTEINDX
SY04200	Company	Comment Master	COMMNTID, CMTSRIES	Predefined document comments (CMMTTEXT)	COMMNTID appears on sales and purchasing documents
SY02300	Company	Posting Setup	SERIES, TRXSOURC	Posting options per transaction source (post to GL, through GL, journals)	Maintained in Tools > Setup > Posting > Posting
SY01100	Company	Posting Accounts Master	SERIES, SEQNUMBR	Default posting accounts by series (ACTINDX)	Join ACTINDX to GL00100 and GL00105 for account numbers
SY40100	Company	Period Setup	YEAR1, PERIODID, SERIES, ODESCTN	Which series and windows are closed per period (CLOSED)	Check before posting into a back period
SY40101	Company	Period Header	YEAR1	Fiscal year first and last day, number of periods, historical year flag	Join SY40100 on YEAR1
SY00300	Company	Account Format Setup	Single setup row	Account framework length and segment layout for the company	Read before building account strings in SQL
SY00600	Company	Location Master (company addresses)	CMPANYID, LOCATNID	Company address IDs used on documents	Not the inventory site table (IV40700)
SY01200	Company	Internet Addresses	Master_Type, Master_ID, ADRSCODE	Email addresses (INET1) and internet info for customers, vendors, company	Master_Type CUS or VEN plus Master_ID and address code
SY03000	Company	Shipping Methods Master	SHIPMTHD	Shipping method IDs and descriptions	SHIPMTHD on SOP and POP documents
SY03300	Company	Payment Terms Master	PYMTRMID	Payment terms and due date rules	PYMTRMID on customers, vendors and documents
MC40000	Company	Multicurrency Setup	FUNLCURR, FUNCRIDX	Functional currency of the company	Join FUNLCURR to DYNAMICS..MC40200

Names follow the GP table descriptions (Tools > Resource Descriptions > Tables in the GP client). In GP 2013 and later the system database can have a name other than DYNAMICS; read SY00100.DBNAME in a company database to find it. Query with read-only logins and never delete from DYNAMICS or tempdb tables while users are logged in.

Want a GP admin query pack?

Tell us your GP version and pain points, and we will send the user, security and lock queries checked for your setup.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Filter by database to see what lives where: company tables hold setup and notes for one company, DYNAMICS holds users, companies, security and activity for the whole install, tempdb holds live locks. Use the key columns to write joins, and the join tip column for the usual next table. Typical tasks: list who has access to which company (SY01400 + SY60100 + SY01500), show roles per user (SY10500 + SY09100), find who holds a record lock (DEX_LOCK + DEX_SESSION + ACTIVITY), or pull notes onto a customer report (RM00101.NOTEINDX + SY03900).

Users: SY01400, company access: SY60100
Companies and database names: SY01500.INTERID
Batches: SY00500 (company) and SY00800 (DYNAMICS)
Notes: SY03900 via NOTEINDX

Where this lives in Dynamics GP

In the GP client, Microsoft Dynamics GP > Tools > Resource Descriptions > Tables lets you search any table by display or physical name and see its fields. Batch status is visible in Batch Recovery (Microsoft Dynamics GP > Tools > Routines > Batch Recovery). User Activity is under Tools > Utilities > System > User Activity. User Access and User Security are under Tools > Setup > System. Each window reads the tables listed here, so the SQL gives the same answer as the window, across all companies at once.

Worked example: clear a stuck batch safely

When a batch is stuck after a workstation crash, first make sure the user is not logged in (no ACTIVITY row, or all users out), then check SY00800 for a row for that batch and confirm Batch Recovery cannot finish it. The commonly used fix is: DELETE DYNAMICS..SY00800 WHERE BACHNUMB = 'RMCSH00012' AND BCHSOURC = 'RM_Cash'; then in the company database UPDATE SY00500 SET BCHSTTUS = 0, MKDTOPST = 0, USERID = '' WHERE BACHNUMB = 'RMCSH00012' AND BCHSOURC = 'RM_Cash'; Take a backup first and check the batch's transactions before reposting.

Version differences and gotchas

From GP 2013 the system database can be named something other than DYNAMICS, so scripts that hardcode DYNAMICS fail on named system databases; read SY00100.DBNAME. Security moved to roles and tasks in GP 10, so older scripts that query pre-GP 10 security tables do not apply. Deleting ACTIVITY or tempdb rows for users who are still working causes data damage; check SQL sessions first. Notes text is stored in TXTFIELD as text, so cast it when exporting.

Frequently Asked Questions

What is the SY00500 table in Dynamics GP?

SY00500 is Posting Definitions Master, the batch header table in each company database. It holds one row per batch with BCHSOURC, BACHNUMB, status (BCHSTTUS), marked-to-post flag (MKDTOPST) and USERID. Stuck batches are usually fixed by resetting BCHSTTUS and MKDTOPST after clearing SY00800.

Which table holds Dynamics GP users?

SY01400 (Users Master) in the DYNAMICS system database holds user IDs and names. Company access is in SY60100, and security role assignments per company are in SY10500. Join SY60100 to SY01500 on CMPANYID to show company names.

Where are notes stored in Dynamics GP?

In SY03900 (Record Notes Master) in each company database. Master records such as customers (RM00101) and vendors (PM00200) have a NOTEINDX column; join it to SY03900.NOTEINDX to read TXTFIELD. The next note index for the company is tracked in SY01500.NOTEINDX in DYNAMICS.

How do I see which GP user is locking a record?

Query tempdb..DEX_LOCK for the table and row, join session_id to tempdb..DEX_SESSION, then join to DYNAMICS..ACTIVITY on SQLSESID to get the USERID and company. If the session is orphaned (no ACTIVITY row), the lock can be cleared after all checks.

If you want your GP admins to ask who is locked, which batches are stuck or who can access a company in plain English on your own servers, we can build that.

Talk to an Expert Explore More Free Tools