Solution to Encyclopedia Brown and the Case of the Data Corruption

This is a continuation from this previous post.

“It’s simple,” Encyclopedia said. “First I ran the following query against our database.”

SELECT SOM.fshipvia
  FROM somast SOM
 WHERE SOM.fsono = '123456’

“The value returned was UGB, which is the actual value for UPS Ground Bill in our database. So, I knew that the Ship Via values were still in the database. ” Encyclopedia said. “To confirm this, I ran the following query”

SELECT SOM.fsono, SOM.fshipvia,
    FROM somast SOM
    LEFT OUTER JOIN cspopup CSP
    ON SOM.fshipvia = CSP.fcpopval
WHERE CSP.identity_column IS NULL

“This returned a long list of sales order numbers which now display no value for Ship Via. The CSPOPUP table holds the values shown on various screens on M2M. M2M doesn’t actually store ‘UPS Grnd Bill’ for every sales order; it stores ‘UGB’ because that takes less space. The ship via values are still there, the screens show nothing because the corresponding records in the values table are gone. This also explains why the ship via information is missing from the sales order documents and the shipping screen and shipping documents. They link to the same table to retrieve the description text.” Encyclopedia said.

“Well, where is the data corruption?” the President asked.

“There is no data corruption. That would suggest that something happened in the system to lose data. This information was deleted by someone at this table.” Encyclopedia said.

The President looked at the others and they indicated that they deleted nothing. “I’ll concede that the records in the cspopup table are gone, but nobody deleted them. It must be a M2M error.”

“One of the people at this table did some maintenance on the list of ship via values two days ago. When they did so, they accidentally deleted some of the ship via values or intentionally did it without realizing the ramifications. Only the people here have rights to do it.” Encyclopedia said.

“How can you tell that? Who did it?” the President asked.

“I pulled the back ups from the past several days and restored them to the test database server for comparison. Remember when you approved the purchase of TOAD for SQL Server six months ago? TOAD verified what happened and I’ve included these screenshots in my report.” Encyclopedia said.

The TOAD Data Compare Wizard.

“TOAD Data Compare is a powerful tool which compares every single record from our current database instance M2MDATA01 to the database which was backed up two nights ago, M2MDATA69. The first record shown shows deleted records from the cspopval table. Notice the record ‘UPS Grnd Bill’ and you see that the arrow points left. That record is missing from our current database instance.“ Encyclopedia said.

“So? We’ve already established that M2M dropped those records in our table,” the President said exasperatedly.

“Yes Sir, but notice the three following records? The double arrow in the middle indicates that these records were changed, not deleted. Someone changed the actual screen text which shows in M2M from ‘FEDEX’ to ‘FED-X’ on all of the appropriate records. While it is possible for a database to lose data, it is not possible that it would randomly change this text or have any idea that those two text fragments mean the same thing to a human being. Therefore, it is reasonable to assume that someone was doing maintenance with this table, and since the other records were deleted on the same day, that the same person also performed the deletions. However, because all transactions in M2M are made with a single account, most often SA, I cannot tell you who made this change.“ Encyclopedia said.

The President looked at his people and said, “Well, at this point it doesn’t matter who did it. Can you fix it? How long will it take?”

“Yes, I can fix it. It’ll take about 3 seconds. TOAD can synchronize the tables for me, or I can use this script to add in the deleted records.” Encyclopedia said.

INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
                                   fcpoptext,
                                   fcpopval,
                                   fnorder,
                                   fcpopacces)
                SELECT CSP69.fcpopkey,
                       CSP69.fcpoptext,
                       CSP69.fcpopval,
                       CSP69.fnorder,
                       CSP69.fcpopacces
                  FROM M2MData69..CSPOPUP CSP69
                       LEFT JOIN m2mdata01..cspopup CS01
                       ON CS01.identity_column = CSP69.identity_column
                 WHERE CSP69.fcpopkey = 'SHIPVIA'
                       AND CS01.identity_column IS NULL

“I checked with Bugs Meany in Made2Manage technical support to report this bug, and he claimed that the system is functioning correctly. It was actually designed to orphan records like this for some reason. To be honest, I was shocked when I tried to delete a ship via in our test company and M2M didn’t stop me.“ Encyclopedia said.

“That’s all well and good Encyclopedia, but what happened to our customer credit terms?” the President asked.

“There’s nothing at all wrong with our customer credit terms. I pulled a back up from six months ago, and used TOAD to compare them. They haven’t changed at all. In fact, I tried to delete terms from the customer screen (CUST) and M2M will not allow you to do so. I think that Jennifer panicked when she thought we were having data corruption, and when you lose faith in your database system you start to ‘find’ all sorts of problems that were never problems to begin with.” Encyclopedia said.

“Encyclopedia,” the President exclaimed “I’m very pleased. I’m going to approve your request to go to the PASS Summit this year.”

Well, now I’m dreaming. Are any of you using TOAD? Are there any comments about my little story or my procedure for handling this problem?