# Dynamics GP System Tables Reference: SY00500, SY01400, SY03900 and the DYNAMICS Database > Source: https://netray.co/tools/dynamics-gp-system-tables-sy-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer The SY, DYNAMICS and tempdb tables GP admins query for stuck batches, users, company access, notes, security roles and logged-in sessions, with key columns and join tips. ## Category microsoft-dynamics ## Erp Microsoft Dynamics GP ## Audience - GP administrator - DBA - Report writer - GP consultant ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Database **Key:** database **Type:** select **Options:** - Company - DYNAMICS - tempdb #### Display name **Key:** technicalName **Type:** text #### Key columns **Key:** keyColumns **Type:** text #### Use it for **Key:** useFor **Type:** text #### Join tip **Key:** joinTip **Type:** text **Rows:** **Table:** SY00500 **Database:** Company **Technical Name:** Posting Definitions Master (batch headers) **Key Columns:** BCHSOURC, BACHNUMB **Use For:** Find stuck or marked batches: BCHSTTUS (batch status), MKDTOPST (marked to post), USERID **Join Tip:** Match SY00800 on BACHNUMB and BCHSOURC plus company name **Table:** SY00800 **Database:** DYNAMICS **Technical Name:** Batch Activity **Key Columns:** BACHNUMB, BCHSOURC, USERID, CMPNYNAM **Use For:** Batches currently open or being posted by a user **Join Tip:** Stale rows here keep a batch locked after a crash **Table:** SY00801 **Database:** DYNAMICS **Technical Name:** Resource Activity **Key Columns:** USERID, CMPNYNAM **Use For:** Resources (such as posting processes) locked by a user **Join Tip:** Clear only for users with no ACTIVITY row **Table:** ACTIVITY **Database:** DYNAMICS **Technical Name:** User Activity (logged-in users) **Key Columns:** USERID, CMPNYNAM, LOGINDAT, LOGINTIM, SQLSESID **Use For:** Who is logged in to which company **Join Tip:** SQLSESID links to DEX_SESSION.session_id in tempdb **Table:** DEX_SESSION **Database:** tempdb **Technical Name:** Dexterity sessions **Key Columns:** session_id, sqlsvr_spid **Use For:** Map a GP session to its SQL Server SPID **Join Tip:** Orphans remain when users are killed; clean with ACTIVITY **Table:** DEX_LOCK **Database:** tempdb **Technical Name:** Dexterity record locks **Key Columns:** session_id, row_id, table_path_name **Use For:** Find which session holds a record lock (for example a document in use) **Join Tip:** Join session_id to DEX_SESSION, then to ACTIVITY.SQLSESID **Table:** SY01400 **Database:** DYNAMICS **Technical Name:** Users Master **Key Columns:** USERID, USERNAME **Use For:** List GP users and their names **Join Tip:** Join SY60100 on USERID for company access **Table:** SY01402 **Database:** DYNAMICS **Technical Name:** User Defaults **Key Columns:** USERID, syDefaultType, SYUSERDFSTR **Use For:** Per-user saved settings and defaults **Join Tip:** One row per user per default type **Table:** SY01500 **Database:** DYNAMICS **Technical Name:** Company Master **Key Columns:** CMPANYID, INTERID, CMPNYNAM **Use For:** Map company ID to database name (INTERID) and display name; next note index (NOTEINDX) **Join Tip:** INTERID = the company database name **Table:** SY60100 **Database:** DYNAMICS **Technical Name:** User-Company Access **Key Columns:** USERID, CMPANYID **Use For:** Which users can open which companies **Join Tip:** Join SY01500 on CMPANYID for names **Table:** SY10500 **Database:** DYNAMICS **Technical Name:** Security Assignment User Role **Key Columns:** USERID, CMPANYID, SECURITYROLEID **Use For:** Roles assigned to a user per company **Join Tip:** Join SY10600 on SECURITYROLEID **Table:** SY10600 **Database:** DYNAMICS **Technical Name:** Security Assignment Role Task **Key Columns:** SECURITYROLEID, SECURITYTASKID **Use For:** Tasks inside each role **Join Tip:** Join SY10700 on SECURITYTASKID **Table:** SY10700 **Database:** DYNAMICS **Technical Name:** Security Assignment Task Operations **Key Columns:** SECURITYTASKID, DICTID, SECRESTYPE, SECURITYID **Use For:** Windows, reports and tables each task grants **Join Tip:** Resolve SECURITYID with the Security Resource Descriptions **Table:** SY09000 **Database:** DYNAMICS **Technical Name:** Security Tasks Master **Key Columns:** SECURITYTASKID **Use For:** Task names and descriptions **Join Tip:** Join from SY10600 **Table:** SY09100 **Database:** DYNAMICS **Technical Name:** Security Roles Master **Key Columns:** SECURITYROLEID **Use For:** Role names and descriptions **Join Tip:** Join from SY10500 **Table:** MC40200 **Database:** DYNAMICS **Technical Name:** Currency Setup **Key Columns:** CURNCYID, CURRNIDX **Use For:** Currency IDs, descriptions and index **Join Tip:** CURRNIDX is the key used in many transaction tables **Table:** DU000020 **Database:** DYNAMICS **Technical Name:** Product versions per company **Key Columns:** companyID, PRODID, versionMajor, versionMinor, versionBuild **Use For:** Check each company is at the same GP and add-in version before an upgrade **Join Tip:** companyID matches SY01500.CMPANYID **Table:** DB_Upgrade **Database:** DYNAMICS **Technical Name:** Database upgrade status **Key Columns:** db_name, PRODID, db_verMajor, db_verMinor, db_verBuild, db_status **Use For:** Find a database or product stuck mid-upgrade **Join Tip:** db_status not 0 means an incomplete upgrade **Table:** SY00100 **Database:** Company **Technical Name:** System Database Setup **Key Columns:** DBNAME **Use For:** Name of the system database the company belongs to **Join Tip:** Must keep the system database name after a test restore **Table:** SY03900 **Database:** Company **Technical Name:** Record Notes Master **Key Columns:** NOTEINDX **Use For:** Note text (TXTFIELD) attached to records **Join Tip:** Join on the NOTEINDX column of the master record, for example RM00101.NOTEINDX **Table:** SY04200 **Database:** Company **Technical Name:** Comment Master **Key Columns:** COMMNTID, CMTSRIES **Use For:** Predefined document comments (CMMTTEXT) **Join Tip:** COMMNTID appears on sales and purchasing documents **Table:** SY02300 **Database:** Company **Technical Name:** Posting Setup **Key Columns:** SERIES, TRXSOURC **Use For:** Posting options per transaction source (post to GL, through GL, journals) **Join Tip:** Maintained in Tools > Setup > Posting > Posting **Table:** SY01100 **Database:** Company **Technical Name:** Posting Accounts Master **Key Columns:** SERIES, SEQNUMBR **Use For:** Default posting accounts by series (ACTINDX) **Join Tip:** Join ACTINDX to GL00100 and GL00105 for account numbers **Table:** SY40100 **Database:** Company **Technical Name:** Period Setup **Key Columns:** YEAR1, PERIODID, SERIES, ODESCTN **Use For:** Which series and windows are closed per period (CLOSED) **Join Tip:** Check before posting into a back period **Table:** SY40101 **Database:** Company **Technical Name:** Period Header **Key Columns:** YEAR1 **Use For:** Fiscal year first and last day, number of periods, historical year flag **Join Tip:** Join SY40100 on YEAR1 **Table:** SY00300 **Database:** Company **Technical Name:** Account Format Setup **Key Columns:** Single setup row **Use For:** Account framework length and segment layout for the company **Join Tip:** Read before building account strings in SQL **Table:** SY00600 **Database:** Company **Technical Name:** Location Master (company addresses) **Key Columns:** CMPANYID, LOCATNID **Use For:** Company address IDs used on documents **Join Tip:** Not the inventory site table (IV40700) **Table:** SY01200 **Database:** Company **Technical Name:** Internet Addresses **Key Columns:** Master_Type, Master_ID, ADRSCODE **Use For:** Email addresses (INET1) and internet info for customers, vendors, company **Join Tip:** Master_Type CUS or VEN plus Master_ID and address code **Table:** SY03000 **Database:** Company **Technical Name:** Shipping Methods Master **Key Columns:** SHIPMTHD **Use For:** Shipping method IDs and descriptions **Join Tip:** SHIPMTHD on SOP and POP documents **Table:** SY03300 **Database:** Company **Technical Name:** Payment Terms Master **Key Columns:** PYMTRMID **Use For:** Payment terms and due date rules **Join Tip:** PYMTRMID on customers, vendors and documents **Table:** MC40000 **Database:** Company **Technical Name:** Multicurrency Setup **Key Columns:** FUNLCURR, FUNCRIDX **Use For:** Functional currency of the company **Join Tip:** Join FUNLCURR to DYNAMICS..MC40200 **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use it **Body:** 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). **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What is the SY00500 table in Dynamics GP? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms dynamics gp sy00500 table, dynamics gp user table, dynamics gp notes table, dynamics gp posting setup, dynamics gp table names, dynamics gp table reference, dynamics gp sy01500, dynamics gp stuck batch sql --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json