Every distribution business eventually runs into a system mystery. Maybe a feature that worked yesterday is broken today, or a user can’t see a button they need.
One small change in your software settings can cause problems that ripple through your whole warehouse. You might find yourself digging through data for hours just to find a simple error.
You shouldn’t have to guess what went wrong. Effective troubleshooting isn’t about luck; it’s about having the right tools.
We are sharing two free SQL queries that give you clear answers so you can get back to business.
Spot Differences in Your Prophet 21 Settings Instantly
The Problem:
When your live Prophet 21 system suddenly starts acting up, it can feel like everything “just broke,” even though it worked yesterday. You may not know who changed what, or which setting is causing the problem.
A different but related issue happens when you finish testing in your sandbox, then move to live, and the process fails. You expect some changes, but the results do not match what you saw in testing, and it is not clear which setting you missed or configured differently.
The Solution:
You can use this read-only query on both cloud and on-premise Prophet 21 systems. It compares settings between your environments and never changes your data.
- For IT Managers: Audit your system settings during migrations or implementations, so you always know what changed and when.
- For Power Users: Quickly check if a setting was toggled off or changed by mistake. This pinpoints problems or rules out settings as a cause, saving you hours of searching.
Real-life Use cases:
- Troubleshooting: Imagine your credit card processing worked yesterday but failed today. This query shows you if a setting was accidentally changed between environments.
- Mergers and Acquisitions on P21: When importing data from another company, check for mismatched P21 settings between environments before you go live.
Using this query, you can pinpoint the exact difference causing the issue or rule out settings as the problem. This saves you hours of guesswork (and headaches).
Get the Query:
/* ================================================================================
* SYSTEM_SETTING TABLE COMPARISON SCRIPT
* ================================================================================
*
* AUTHOR: Conveyance Solutions (Evan Foster)
* DATE: 12/01/2025
*
* DESCRIPTION:
* Compares the system_setting table between two P21 databases and identifies:
* - Settings that exist only in Database 1
* - Settings that exist only in Database 2
* - Settings that exist in both but have different values
*
* Results are pivoted to show one row per different column, making it easy
* to see exactly which field differs and the values in each database.
*
* AUDIT COLUMNS EXCLUDED FROM COMPARISON:
* - date_created
* - date_last_modified
* - last_maintained_by
*
* TABLE SCHEMA:
* Primary Key: system_setting_uid
* Comparison Fields: configuration_id, module_cd, name, value, data_type_cd,
* data_type_length, data_type_scale
*
* ================================================================================
* INSTRUCTIONS - READ BEFORE RUNNING:
* ================================================================================
*
* STEP 1: ENABLE SQLCMD MODE
* This script REQUIRES SQLCMD mode in SQL Server Management Studio (SSMS).
*
* To enable:
* - In SSMS menu bar, click "Query" → Check "SQLCMD Mode"
*
* Without SQLCMD mode, you will get syntax errors on the :setvar lines.
*
* STEP 2: SET DATABASE NAMES (LINES 47-48 BELOW)
* Modify the :setvar statements to specify which databases to compare:
*
* :setvar DB1 YourFirstDatabaseName
* :setvar DB2 YourSecondDatabaseName
*
* Example:
* :setvar DB1 P21_Production
* :setvar DB2 P21_Test
*
* STEP 3: EXECUTE THE SCRIPT
* - Press F5 or click Execute
* - Review the four result sections:
* 1. Settings only in DB1
* 2. Settings only in DB2
* 3. Settings with different values (pivoted)
* 4. Summary statistics
*
* OUTPUT FORMAT (Section 3 - Different Values):
* system_setting_uid | SettingName | ColumnName | Value_in_DB1 | Value_in_DB2
*
* Each row shows ONE field that differs. If a setting has multiple different
* fields, it will appear on multiple rows.
*
* ================================================================================
*/
-- STEP 2: MODIFY THESE DATABASE NAMES TO MATCH YOUR ENVIRONMENT
:setvar DB1 ConveyanceP21
:setvar DB2 ConveyanceP21DEV
USE $(DB1)
SET NOCOUNT ON
PRINT '========================================='
PRINT 'SYSTEM_SETTING COMPARISON'
PRINT 'Database 1: $(DB1)'
PRINT 'Database 2: $(DB2)'
PRINT 'Generated: ' + CONVERT(VARCHAR, GETDATE(), 120)
PRINT '========================================='
PRINT ''
-- First, verify the table exists in both databases
IF NOT EXISTS (SELECT 1 FROM $(DB1).sys.tables WHERE name = 'system_setting')
BEGIN
PRINT 'ERROR: system_setting table does not exist in $(DB1)'
RETURN
END
IF NOT EXISTS (SELECT 1 FROM $(DB2).sys.tables WHERE name = 'system_setting')
BEGIN
PRINT 'ERROR: system_setting table does not exist in $(DB2)'
RETURN
END
-- Get counts
DECLARE @CountDB1 INT, @CountDB2 INT
SELECT @CountDB1 = COUNT(*) FROM $(DB1).dbo.system_setting
SELECT @CountDB2 = COUNT(*) FROM $(DB2).dbo.system_setting
PRINT 'Record count in $(DB1): ' + CAST(@CountDB1 AS VARCHAR)
PRINT 'Record count in $(DB2): ' + CAST(@CountDB2 AS VARCHAR)
PRINT ''
-- Compare by system_setting_uid
PRINT '========================================='
PRINT 'SETTINGS ONLY IN $(DB1)'
PRINT '========================================='
SELECT
db1.system_setting_uid,
db1.configuration_id,
db1.module_cd,
db1.name,
db1.value,
db1.data_type_cd,
db1.data_type_length,
db1.data_type_scale
FROM $(DB1).dbo.system_setting db1
WHERE NOT EXISTS (
SELECT 1
FROM $(DB2).dbo.system_setting db2
WHERE db1.system_setting_uid = db2.system_setting_uid
)
ORDER BY db1.name
DECLARE @OnlyInDB1 INT = @@ROWCOUNT
PRINT ''
PRINT 'Count: ' + CAST(@OnlyInDB1 AS VARCHAR)
PRINT ''
-- Settings only in DB2
PRINT '========================================='
PRINT 'SETTINGS ONLY IN $(DB2)'
PRINT '========================================='
SELECT
db2.system_setting_uid,
db2.configuration_id,
db2.module_cd,
db2.name,
db2.value,
db2.data_type_cd,
db2.data_type_length,
db2.data_type_scale
FROM $(DB2).dbo.system_setting db2
WHERE NOT EXISTS (
SELECT 1
FROM $(DB1).dbo.system_setting db1
WHERE db1.system_setting_uid = db2.system_setting_uid
)
ORDER BY db2.name
DECLARE @OnlyInDB2 INT = @@ROWCOUNT
PRINT ''
PRINT 'Count: ' + CAST(@OnlyInDB2 AS VARCHAR)
PRINT ''
-- Settings with different values (excluding audit columns)
PRINT '========================================='
PRINT 'SETTINGS WITH DIFFERENT VALUES'
PRINT '========================================='
-- Create temp table to store differences
IF OBJECT_ID('tempdb..#Differences') IS NOT NULL DROP TABLE #Differences
SELECT
db1.system_setting_uid,
db1.name,
db1.configuration_id AS db1_configuration_id,
db2.configuration_id AS db2_configuration_id,
db1.module_cd AS db1_module_cd,
db2.module_cd AS db2_module_cd,
db1.value AS db1_value,
db2.value AS db2_value,
db1.data_type_cd AS db1_data_type_cd,
db2.data_type_cd AS db2_data_type_cd,
db1.data_type_length AS db1_data_type_length,
db2.data_type_length AS db2_data_type_length,
db1.data_type_scale AS db1_data_type_scale,
db2.data_type_scale AS db2_data_type_scale
INTO #Differences
FROM $(DB1).dbo.system_setting db1
INNER JOIN $(DB2).dbo.system_setting db2
ON db1.system_setting_uid = db2.system_setting_uid
WHERE
ISNULL(db1.configuration_id, -999) <> ISNULL(db2.configuration_id, -999)
OR ISNULL(db1.module_cd, -999) <> ISNULL(db2.module_cd, -999)
OR ISNULL(db1.value, '') <> ISNULL(db2.value, '')
OR ISNULL(db1.data_type_cd, -999) <> ISNULL(db2.data_type_cd, -999)
OR ISNULL(db1.data_type_length, -999) <> ISNULL(db2.data_type_length, -999)
OR ISNULL(db1.data_type_scale, -999) <> ISNULL(db2.data_type_scale, -999)
DECLARE @DifferentValues INT = @@ROWCOUNT
-- Display pivoted results
SELECT
system_setting_uid,
name AS SettingName,
ColumnName,
[$(DB1)] AS [Value_in_$(DB1)],
[$(DB2)] AS [Value_in_$(DB2)]
FROM (
SELECT system_setting_uid, name, 'configuration_id' AS ColumnName,
CAST(db1_configuration_id AS VARCHAR(255)) AS [$(DB1)],
CAST(db2_configuration_id AS VARCHAR(255)) AS [$(DB2)]
FROM #Differences
WHERE ISNULL(db1_configuration_id, -999) <> ISNULL(db2_configuration_id, -999)
UNION ALL
SELECT system_setting_uid, name, 'module_cd',
CAST(db1_module_cd AS VARCHAR(255)),
CAST(db2_module_cd AS VARCHAR(255))
FROM #Differences
WHERE ISNULL(db1_module_cd, -999) <> ISNULL(db2_module_cd, -999)
UNION ALL
SELECT system_setting_uid, name, 'value',
ISNULL(db1_value, 'NULL'),
ISNULL(db2_value, 'NULL')
FROM #Differences
WHERE ISNULL(db1_value, '') <> ISNULL(db2_value, '')
UNION ALL
SELECT system_setting_uid, name, 'data_type_cd',
CAST(db1_data_type_cd AS VARCHAR(255)),
CAST(db2_data_type_cd AS VARCHAR(255))
FROM #Differences
WHERE ISNULL(db1_data_type_cd, -999) <> ISNULL(db2_data_type_cd, -999)
UNION ALL
SELECT system_setting_uid, name, 'data_type_length',
CAST(db1_data_type_length AS VARCHAR(255)),
CAST(db2_data_type_length AS VARCHAR(255))
FROM #Differences
WHERE ISNULL(db1_data_type_length, -999) <> ISNULL(db2_data_type_length, -999)
UNION ALL
SELECT system_setting_uid, name, 'data_type_scale',
CAST(db1_data_type_scale AS VARCHAR(255)),
CAST(db2_data_type_scale AS VARCHAR(255))
FROM #Differences
WHERE ISNULL(db1_data_type_scale, -999) <> ISNULL(db2_data_type_scale, -999)
) AS Unpivoted
ORDER BY name, ColumnName
DROP TABLE #Differences
PRINT ''
PRINT 'Count: ' + CAST(@DifferentValues AS VARCHAR)
PRINT ''
-- Summary
PRINT '========================================='
PRINT 'SUMMARY'
PRINT '========================================='
PRINT ''
PRINT 'Total in $(DB1): ' + CAST(@CountDB1 AS VARCHAR)
PRINT 'Total in $(DB2): ' + CAST(@CountDB2 AS VARCHAR)
PRINT 'Only in $(DB1): ' + CAST(@OnlyInDB1 AS VARCHAR)
PRINT 'Only in $(DB2): ' + CAST(@OnlyInDB2 AS VARCHAR)
PRINT 'Different Values: ' + CAST(@DifferentValues AS VARCHAR)
PRINT 'Matching: ' + CAST(@CountDB1 - @OnlyInDB1 - @DifferentValues AS VARCHAR)
IF @OnlyInDB1 = 0 AND @OnlyInDB2 = 0 AND @DifferentValues = 0
BEGIN
PRINT ''
PRINT '*** ALL SETTINGS MATCH! ***'
END
ELSE
BEGIN
PRINT ''
PRINT '*** DIFFERENCES FOUND - See details above ***'
END
PRINT ''
PRINT '========================================='
PRINT 'COMPARISON COMPLETE'
PRINT '========================================='
SET NOCOUNT OFF
Quickly Troubleshoot and Verify Module Access in P21
The Problem:
A user complains they can’t do their job, but everything looks fine on your screen. Trying to fix this over email or the phone can take hours, even days.
The Solution:
If you’re on on-premises or self-hosted Prophet 21, this script can be a huge time-saver. You see exactly what they see and what they’re allowed to do.
It temporarily changes your network name so you can log in with your own credentials but see the system as if you were someone else. You can spot permission errors immediately.
- Verify Permissions: Instantly check what the user can see and do—perfect for confirming role-based access, like ensuring accounting only sees financial modules.
- Replicate Errors: Experience the same error messages and issues in real-time, so you can address problems without back-and-forth guesswork.
See It in Action:
If a warehouse manager gets a weird error, don’t waste time guessing. Use this script to become them for a moment. You can test a fix, see if it works, and resolve the ticket without ever picking up the phone.
More Real-life Use Cases:
- Fixing “None” Errors: These vague errors can be hard to trace and often take a lot of trial and error to resolve. By using this script, you can reproduce the issue yourself and work through the troubleshooting steps without pulling the end user into every test, which helps you resolve the problem faster.
Get the Query:
-- Query to view current network_name assignments for all users
SELECT
u.network_name,
u.*
FROM users u;
-- STEP 1: Impersonate an end user
-- Update your user's network_name to match the end user you want to impersonate
UPDATE users
SET network_name = 'END_USER' -- Replace with the target end user's network_name
WHERE id = 'YOUR_USER'; -- Replace with your own user ID
-- STEP 2: Revert impersonation
-- Set your network_name back to NULL to return to your own account
UPDATE users
SET network_name = NULL
WHERE id = 'YOUR_USER'; -- Replace with your own user ID
Don’t Let System Changes Slow You Down
You aren’t on your own when it comes to tackling Prophet 21 challenges. These simple tools can save you hours of frustration and keep your operations running smoothly.
At Conveyance Solutions, we are expert P21 implementers who help you get the most out of the Epicor ERP. From fixing small glitches to managing full-scale deployments, we work as an extension of your team. We help you identify pitfalls early and build a roadmap that works for you.
Talk to one of our experts today to learn how we can help you optimize your system.
Ready to see the difference a Platinum Partner can make?
Learn more about how we can help make data work better for you.
