In the past couple of weeks, I have discussed the problems of orphaned records in the M2M Database and promised to provide a script to find them. So, I’ve decided to turn it into a game.
If you’d like to play, paste the following script into your Query Analyzer or SQL Server Management Studio and run it. It will return the count of orphaned records in each table. Copy and paste the results grid into the comments of the blog so we can compare. Here’s the script:
(SELECT 'Sales Order Releases' AS [Type], count(sor.IDENTITY_COLUMN) FROM SORELS SOR LEFT OUTER JOIN SOITEM SOI ON SOI.FSONO = SOR.FSONO AND SOI.FINUMBER = SOR.FINUMBER LEFT OUTER JOIN SOMAST SOM ON SOM.FSONO = SOR.FSONO LEFT OUTER JOIN SLCDPM SLC ON SLC.FCUSTNO = SOM.FCUSTNO WHERE SOI.FSONO IS NULL OR SOM.FSONO IS NULL OR SLC.FCUSTNO IS NULL) UNION (SELECT 'Sales Order Items' AS [Type], count(SOI.IDENTITY_COLUMN) FROM SOITEM SOI LEFT OUTER JOIN SOMAST SOM ON SOM.FSONO = SOI.FSONO LEFT OUTER JOIN SLCDPM SLC ON SLC.FCUSTNO = SOM.FCUSTNO WHERE SOM.FSONO IS NULL OR SLC.FCUSTNO IS NULL) UNION (SELECT 'Sales Order Master' AS [Type], count(SOM.IDENTITY_COLUMN) FROM SOMAST SOM LEFT OUTER JOIN SLCDPM SLC ON SLC.FCUSTNO = SOM.FCUSTNO WHERE SLC.FCUSTNO IS NULL) UNION (SELECT 'Quote Items' AS [Type], count(qtitem.IDENTITY_COLUMN) FROM qtitem LEFT OUTER JOIN QTMAST ON qtmast.fquoteno = qtitem.FQUOTENO LEFT OUTER JOIN slcdpm ON slcdpm.FCUSTNO = qtmast.FCUSTNO WHERE qtmast.FQUOTENO IS NULL OR slcdpm.FCUSTNO IS NULL) UNION (SELECT 'Quote Master' AS [Type], count(qtmast.IDENTITY_COLUMN) FROM qtmast LEFT OUTER JOIN slcdpm ON slcdpm.FCUSTNO = qtmast.FCUSTNO WHERE slcdpm.FCUSTNO IS NULL) UNION (SELECT 'Shipping' AS [Type], count(shitem.IDENTITY_COLUMN) FROM shitem LEFT OUTER JOIN SHMAST ON shmast.fshipno = shitem.fshipno WHERE shmast.FSHIPNO IS NULL) UNION (SELECT 'Accts Recv Items' AS [Type], count(aritem.IDENTITY_COLUMN) FROM aritem LEFT OUTER JOIN armast ON aritem.FCINVOICE = armast.FCINVOICE LEFT OUTER JOIN slcdpm ON slcdpm.FCUSTNO = aritem.FCUSTNO WHERE armast.FCINVOICE IS NULL OR slcdpm.FCUSTNO IS NULL) UNION (SELECT 'Accts Recv Master' AS [Type], count(armast.IDENTITY_COLUMN) FROM armast LEFT OUTER JOIN SLCDPM ON slcdpm.FCUSTNO = armast.FCUSTNO WHERE slcdpm.FCUSTNO IS NULL) UNION (SELECT 'Accts Payable Items' AS [Type], count(apitem.IDENTITY_COLUMN) FROM apitem LEFT OUTER JOIN apmast ON apitem.FCINVKEY = (apmast.fvendno + apmast.fcinvoice) LEFT OUTER JOIN APVEND ON APVEND.FVENDNO = apmAST.FVENDNO WHERE apmast.IDENTITY_COLUMN IS NULL OR APVEND.FVENDNO IS NULL) UNION (SELECT 'Accts Payable Master' AS [Type], count(apmast.IDENTITY_COLUMN) FROM apmast LEFT OUTER JOIN APVEND ON APVEND.FVENDNO = apmAST.FVENDNO WHERE APVEND.FVENDNO IS NULL) UNION (SELECT 'General Ledger' AS [Type], count(GLITEM.IDENTITY_COLUMN) FROM GLITEM LEFT OUTER JOIN glmast ON glmast.fcacctnum = glitem.FCACCTNUM WHERE glmast.FCACCTNUM IS NULL) UNION (SELECT 'Journal Entry' AS [Type], count(gljeitem.IDENTITY_COLUMN) FROM gljeitem LEFT OUTER JOIN gljemast ON gljemast.fcentry = gljeitem.fcentry WHERE gljemast.fcentry IS NULL) UNION (SELECT 'Jobs' AS [Type], count(joitem.IDENTITY_COLUMN) FROM joitem LEFT OUTER JOIN jomast ON jomast.FJOBNO = joitem.FJOBNO WHERE jomast.FJOBNO IS NULL) UNION (SELECT 'Receiving Items' AS [Type], count(rcitem.IDENTITY_COLUMN) FROM rcitem LEFT OUTER JOIN rcmast ON rcmast.FRECEIVER = rcitem.FRECEIVER WHERE rcmast.FRECEIVER IS NULL) UNION (SELECT 'Bill of Lading' AS [Type], count(bolitem.IDENTITY_COLUMN) FROM BOLITEM LEFT OUTER JOIN BOLMAST ON bolmast.FCBOLNO = bolitem.FCBOLNO WHERE bolmast.FCBOLNO IS NULL) UNION (SELECT 'Purchase Items' AS [Type], count(poitem.IDENTITY_COLUMN) FROM poitem LEFT OUTER JOIN pomast ON pomast.FPONO = poitem.FPONO WHERE pomast.FPONO IS NULL) UNION (SELECT 'Purchase Master' AS [Type], count(pomast.IDENTITY_COLUMN) FROM pomast LEFT OUTER JOIN apvend ON pomast.FVENDNO = apvend.FVENDNO WHERE apvend.FVENDNO IS NULL) UNION (SELECT 'RFQ' AS [Type], count(poqitm.IDENTITY_COLUMN) FROM poqitm LEFT OUTER JOIN POQREC ON POQREC.fpartno + POQREC.frev + POQREC.fac = poqitm.fpartno + poqitm.fcpartrev + poqitm.fac WHERE poqrec.IDENTITY_COLUMN IS NULL) UNION (SELECT 'Inventory Vendors' AS [Type], count(invend.IDENTITY_COLUMN) FROM INVEND LEFT OUTER JOIN INMAST ON (inmast.FPARTNO + inmast.FREV + inmast.FAC) = (invend.FPARTNO + invend.FPARTREV + invend.FAC) WHERE inmast.IDENTITY_COLUMN IS NULL) UNION (SELECT 'Current Rev' AS [Type], count(invcur.IDENTITY_COLUMN) FROM INVCUR LEFT OUTER JOIN INMAST ON (INVCUR.fcpartno + INVCUR.fcpartrev + INVCUR.fac) = (inmast.FPARTNO + inmast.FREV + inmast.fac) WHERE inmast.FPARTNO IS NULL) UNION (SELECT 'Part Cross Ref' AS [Type], count(incros.IDENTITY_COLUMN) FROM incros LEFT OUTER JOIN inmast ON (incros.fpartno + incros.fcpartrev + incros.fac) = (inmast.FPARTNO + inmast.FREV + inmast.fac) WHERE inmast.FPARTNO IS NULL) UNION (SELECT 'BOMs' AS [Type], count(INBOMS.IDENTITY_COLUMN) FROM INBOMS LEFT OUTER JOIN INBOMM ON (INBOMS.fparent + INBOMS.fparentrev + INBOMS.pfacilityid) = (INBOMM.fpartno + INBOMM.fcpartrev + INBOMM.facilityid) WHERE inboms.fparent IS NULL) |
If you have orphaned records and you want to see which records are orphaned, you can download this script and run it. It will show you details of the records found above.
What should I do about orphaned records?
Well, I would contact support to evaluate your problem and provide scripts to delete the records. If you aren’t on support, you may want to contract with a M2M consultant, such as myself, to verify that the records are un-needed and delete them.
So, what’s your score?
Here are the results for one of the databases for which I am responsible.
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 7
Accts Recv Master 3
Bill of Lading 0
BOMs 0
Current Rev 3572
General Ledger 0
Inventory Vendors 709
Jobs 0
Journal Entry 3
Part Cross Ref 35
Purchase Items 4
Purchase Master 0
Quote Items 302
Quote Master 229
Receiving Items 7
RFQ 0
Sales Order Items 8
Sales Order Master 4
Sales Order Releases 27
Shipping 53
Hmm….. thanks for the script.
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 3
Accts Recv Master 2
Bill of Lading 0
BOMs 0
Current Rev 168
General Ledger 0
Inventory Vendors 185
Jobs 0
Journal Entry 0
Part Cross Ref 44
Purchase Items 0
Purchase Master 0
Quote Items 0
Quote Master 0
Receiving Items 2
RFQ 0
Sales Order Items 82
Sales Order Master 0
Sales Order Releases 81
Shipping 0
Hopefully you get a big enough dataset to find some patterns..
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 0
Accts Recv Master 0
Bill of Lading 0
BOMs 0
Current Rev 24
General Ledger 0
Inventory Vendors 183
Jobs 0
Journal Entry 1
Part Cross Ref 66
Purchase Items 0
Purchase Master 0
Quote Items 1
Quote Master 0
Receiving Items 9
RFQ 0
Sales Order Items 0
Sales Order Master 1
Sales Order Releases 0
Shipping 0
Sales Order Releases 495
Sales Order Items 474
Sales Order Master 140
Quote Items 238
Quote Master 11
Shipping 330
Accts Recv Items 891
Accts Recv Master 197
Accts Payable Items 0
Accts Payable Master 0
General Ledger 0
Journal Entry 1
Jobs 1
Receiving Items 100
Bill of Lading 3
Purchase Items 26
Purchase Master 0
RFQ 0
Inventory Vendors 1865
Current Rev 30483
Part Cross Ref 193
BOMs 0
I think that makes me the king of Current Rev; 30,483 /flex
Jason, your database is a huge mess.
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 0
Accts Recv Master 0
Bill of Lading 0
BOMs 0
Current Rev 163
General Ledger 0
Inventory Vendors 326
Jobs 0
Journal Entry 7
Part Cross Ref 19
Purchase Items 6
Purchase Master 0
Quote Items 0
Quote Master 0
Receiving Items 44
RFQ 0
Sales Order Items 0
Sales Order Master 0
Sales Order Releases 3
Shipping 0
Been snooping on the blog for sometime, first time I’ve posted. Great blog, please keep it up! I encountered an orphaned records issue with the barcode labor tables (LAMAST) where it was not adding master records, just detail records (to LADETAIL). Seems to cause no issues other than making edits hard. M2M could not figure it out. First they refused to believe it. In the end, in fairness, they were supportive, after some remote connections and various emails they finally asked me to FTP the database. By that time I was spending too much time on an issue that really was causing me no pain, so I let it drop. But the results here are scary, these are “important” tables in the core of the system… My results are enclosed. Would it be interesting to rerun the script with a total records count to calculate a percentage? I assume we all have vastly different table sizes. i.e. some of you probably add way more RFQ’s in a given week than I do…
Thanks again for the forum.
Sales Order Releases 3
Sales Order Items 1
Sales Order Master 1
Quote Items 8
Quote Master 3
Shipping 1
Accts Recv Items 0
Accts Recv Master 0
Accts Payable Items 20
Accts Payable Master 2
General Ledger 0
Journal Entry 0
Jobs 1
Receiving Items 5
Bill of Lading 0
Purchase Items 1
Purchase Master 0
RFQ 1918
Inventory Vendors 2264
Current Rev 6
Part Cross Ref 251
BOMs 0
Ben, did they ask you to run a profile trace? What did the trace show when you added Labor?
I will have some upcoming tutorials on how to easily use Profiler to diagnose problems and make report customization easier.
Thank you for sharing your script, sense of humor and knowledge.
Accts Payable Items 1
Accts Payable Master 2
Accts Recv Items 0
Accts Recv Master 0
Bill of Lading 0
BOMs 0
Current Rev 58
General Ledger 0
Inventory Vendors 120
Jobs 1
Journal Entry 0
Part Cross Ref 1
Purchase Items 0
Purchase Master 1
Quote Items 0
Quote Master 0
Receiving Items 0
RFQ 0
Sales Order Items 4
Sales Order Master 1
Sales Order Releases 8
Shipping 0
They did not ask me to run a profile trace, I’d love to learn how.
Thanks for the script.
Sales Order Releases 5
Sales Order Items 4
Sales Order Master 2
Quote Items 0
Quote Master 0
Shipping 0
Accts Recv Items 8
Accts Recv Master 3
Accts Payable Items 0
Accts Payable Master 0
General Ledger 0
Journal Entry 0
Jobs 2
Receiving Items 1
Bill of Lading 0
Purchase Items 5
Purchase Master 1
RFQ 0
Inventory Vendors 1008
Current Rev 3701
Part Cross Ref 3
BOMs 0
Ben, it’s also important to note that this doesn’t include every table that should be checked. I only included the tables I felt were important, and I completely forgot about labor. I’ll look into adding the orphaned script for those tables too.
I’m glad everyone seems to like the scripts.
David,
My hope is that someone from M2M sees these numbers of orphaned records that we all have, and decides that it would be important to help us clean this up, and correct the programs that are allowing/creating orphans in the first place.
Fred
David
I’ve started a case file. M2M provided an executable to run, then I’m to post the results to them so they may follow up with a correction file.
PS: They recognized your script file so they are keeping a pulse on your URL.
Thanks again
Looks like I have the most!
Accts Payable Items 16
Accts Payable Master 0
Accts Recv Items 8048
Accts Recv Master 3549
Bill of Lading 0
BOMs 0
Current Rev 7067
General Ledger 0
Inventory Vendors 514
Jobs 0
Journal Entry 0
Part Cross Ref 14
Purchase Items 0
Purchase Master 0
Quote Items 350
Quote Master 69
Receiving Items 0
RFQ 0
Sales Order Items 2747
Sales Order Master 1010
Sales Order Releases 2770
Shipping 9
Nice quary.
I looked at your query a little closer and realized why I have so many records. My company archived several years ago (before I worked for them). Aparently when they did so, they removed some data that was linked to other tables. For example my SOMAST refrences many customers that aparently were removed from my database when the archive was done.
Your query also does not seem to take into account some null records. For example I don’t think it would pick up on SOMAST.FSONO being null.
Thanks Scott. As I mentioned before the script is a work in progress. In fact, I’m just testing a new portion for checking labor records. However, in reference to the somast.fsono, I know I didn’t check everything. I suppose I should be checking every table which has a required field (or rather it would if the database was written properly) and check for nulls.
I’ll keep working on it and posting comments as I improve the script.
I also want to check how archiving (which I’ve never done) would affect the results.
Thanks.
Accts Payable Items 24
Accts Payable Master 28
Accts Recv Items 421
Accts Recv Master 228
Bill of Lading 0
BOMs 0
Current Rev 4444
General Ledger 0
Inventory Vendors 4743
Jobs 0
Journal Entry 0
Part Cross Ref 15
Purchase Items 1
Purchase Master 2
Quote Items 6
Quote Master 5
Receiving Items 392
RFQ 0
Sales Order Items 608
Sales Order Master 93
Sales Order Releases 700
Shipping 26
Looks like I have the most for Inventory Vendors… Yeesh.
Post M2M DatavalidationUI results
Accts Payable Items 0
Accts Payable Master 2
Accts Recv Items 0
Accts Recv Master 0
Bill of Lading 0
BOMs 0
Current Rev 0
General Ledger 0
Inventory Vendors 0
Jobs 1
Journal Entry 0
Part Cross Ref 0
Purchase Items 0
Purchase Master 1
Quote Items 0
Quote Master 0
Receiving Items 0
RFQ 0
Sales Order Items 1
Sales Order Master 1
Sales Order Releases 4
Shipping 0
[…] Please respond by copying and pasting the grid into the comments area, like people did with the Orphaned Records Game. DECLARE @TableName VARCHAR(100) –For storing values in the cursor –Cursor to get […]
Sales Order Releases 10
Sales Order Items 10
Sales Order Master 2
Quote Items 8
Quote Master 2
Shipping 0
Accts Recv Items 3
Accts Recv Master 1
Accts Payable Items 0
Accts Payable Master 0
General Ledger 0
Journal Entry 0
Jobs 0
Receiving Items 0
Bill of Lading 0
Purchase Items 38
Purchase Master 8
RFQ 0
Inventory Vendors 169
Current Rev 93
Part Cross Ref 0
BOMs 0
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 9
Accts Recv Master 4
Bill of Lading 0
BOMs 0
Current Rev 603
General Ledger 0
Inventory Vendors 1829
Jobs 10
Journal Entry 1
Part Cross Ref 54
Purchase Items 0
Purchase Master 0
Quote Items 3
Quote Master 3
Receiving Items 2
RFQ 0
Sales Order Items 2
Sales Order Master 3
Sales Order Releases 2
Shipping 5
[…] deleted a couple of educational and testing companies which many people used. However, like many other areas of M2M, the records are not properly deleted and cleaned […]
Thanks for this, David. Very insightful stuff.
Sales Order Releases 20
Sales Order Items 5
Sales Order Master 2
Quote Items 0
Quote Master 0
Shipping 0
Accts Recv Items 10
Accts Recv Master 2
Accts Payable Items 0
Accts Payable Master 0
General Ledger 0
Journal Entry 4
Jobs 2
Receiving Items 39
Bill of Lading 0
Purchase Items 0
Purchase Master 0
RFQ 0
Inventory Vendors 106
Current Rev 687
Part Cross Ref 74
BOMs 0
A note about “Inventory Vendors”:
If you make a habit of buying non-standard parts, expect a big number. Common reasons: one-of-a-kind production requirements, and all of your assorted support buys (supplies, maintenance parts, etc.)
It’s a logical by-product of the system. Those purchases had to have a part number and a vendor, but there’s no compelling reason for creating a new item master record.
Sales Order Releases 236
Sales Order Items 232
Sales Order Master 31
Quote Items 28
Quote Master 1
Shipping 0
Accts Recv Items 301
Accts Recv Master 59
Accts Payable Items 6
Accts Payable Master 5
General Ledger 0
Journal Entry 0
Jobs 0
Receiving Items 24
Bill of Lading 0
Purchase Items 4
Purchase Master 0
RFQ 7
Inventory Vendors 1246
Current Rev 20
Part Cross Ref 8
BOMs 0
Looks like our database is in need of some TLC
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 24
Accts Recv Master 4
Bill of Lading 0
BOMs 0
Current Rev 2843
General Ledger 0
Inventory Vendors 425
Jobs 1
Journal Entry 4
Part Cross Ref 3
Purchase Items 68133
Purchase Master 0
Quote Items 33
Quote Master 1
Receiving Items 135
RFQ 0
Sales Order Items 122
Sales Order Master 124
Sales Order Releases 397
Shipping 13
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 3
Accts Recv Master 1
Bill of Lading 0
BOMs 0
Current Rev 174
General Ledger 0
Inventory Vendors 4
Jobs 0
Journal Entry 0
Part Cross Ref 0
Purchase Items 0
Purchase Master 0
Quote Items 0
Quote Master 0
Receiving Items 0
RFQ 0
Sales Order Items 1
Sales Order Master 1
Sales Order Releases 1
Shipping 0
David – I look forward to seeing you at this years user conference. This has become a large issue for me that I can’t seem to get resolved. The resolution I get from support is that my users are not shutting down correctly.
Accts Payable Items 0
Accts Payable Master 0
Accts Recv Items 9128
Accts Recv Master 1
Bill of Lading 646
BOMs 0
Current Rev 382
General Ledger 0
Inventory Vendors 16
Jobs 0
Journal Entry 0
Part Cross Ref 39
Purchase Items 230
Purchase Master 3
Quote Items 0
Quote Master 0
Receiving Items 0
RFQ 0
Sales Order Items 409
Sales Order Master 9
Sales Order Releases 29997
Shipping 6
How does one reindex or otherwise cleanup the SQL/Foxpro database/tables.
I’m getting some strange errors and would like to at least take this out of the equation. Someone removed all my reference documentation from my office when I was out for surgery.
Also any insight as to which utilities to run for mainentenance and how often would be greatly appreciated. Much thanks in advance!
Larry,
What version of M2M are you using? Are you currently on maintenance? If so, I’d give them a call.
If you like, e-mail me at david[at]made2mentor.com and we can speak further about your issues.
Accts Payable Items 2
Accts Payable Master 0
Accts Recv Items 17
Accts Recv Master 3
Bill of Lading 27
BOMs 0
Current Rev 134
General Ledger 0
Inventory Vendors 6028
Jobs 0
Journal Entry 0
Part Cross Ref 421
Purchase Items 6
Purchase Master 1
Quote Items 0
Quote Master 0
Receiving Items 4
RFQ 0
Sales Order Items 6
Sales Order Master 5
Sales Order Releases 54
Shipping 0
Yes, we do have lots of orphan records,
Now How do I delete them?
Thank
John
Accts Payable Items 5
Accts Payable Master 0
Accts Recv Items 6
Accts Recv Master 5
Bill of Lading 0
BOMs 0
Current Rev 11143
General Ledger 0
Inventory Vendors 1740
Jobs 0
Journal Entry 1
Part Cross Ref 20
Purchase Items 53
Purchase Master 4
Quote Items 0
Quote Master 0
Receiving Items 1
RFQ 85
Sales Order Items 15
Sales Order Master 6
Sales Order Releases 15
Shipping 2
Make me wish we were on maintenance!!!
Accts Payable Items 3
Accts Payable Master 66
Accts Recv Items 44
Accts Recv Master 25
Bill of Lading 0
BOMs 0
Current Rev 11329
General Ledger 0
Inventory Vendors 11681
Jobs 1
Journal Entry 0
Part Cross Ref 110
Purchase Items 3608
Purchase Master 3
Quote Items 31
Quote Master 5
Receiving Items 4
RFQ 80
Sales Order Items 110
Sales Order Master 43
Sales Order Releases 126
Shipping 157
So you advise running a script to blindy delete all of these records…… really……? What is that script? Will you be responsible if there are any negative results.
It might be important to note some issues that could be causing orphans, such as customziations, network issues, etc.
bom, there’s an interesting story there, which I haven’t related. However, if you want those records cleaned up, and you’re on M2M support, they can issue a DVU which will perform data validation on your system and report the results to Consona. If they approve, they can send you a code to cause the DVU to clean up those orphans.
Also, this post helped people find problems, such as Bob mentioned, with their own customizations which were causing those orphans as well.