The "shotgun approach" is to more easily __find__ these data issues.

They have to be resolved one at a time.

Some of them are flat out bad data and the whole row can be deleted. However, one must look to see for sure what needs to be done.

Like I said these are old bugs that should have not done this in the first place and the program has been fixed to stop writing the bad data, I just need a way to find it, since something that happened years ago is causing a report on the current year to be wrong.

These are situations that are invisible to the user, or a way to fix them is unavailable to them.

I know that development does not have the time to give us reports or anything else to handle this. This may only be a handful of clients, but having one more tool in my toolbox to identify bad data without having to code a SQL statement for each table will be extremely helpful. This script does exactly that, THANKS!!!

Unfortunately, our clients don't always pay attention and catch these things when they first occurred and 6 or 7 years later now it is a problem. This points out that if one client has this, then others might have this, so I am just trying to be prepared.

Thanks for the script, that will go a long way to speed up finding issues to evaluate for fixing.

Also, thanks for the concern to make sure I am not clueless when it comes to fixing data. I don't know how the programming language that is used allows things like this to happen, but an old version of the programming language would let it write data when that meant duplicate keys. When we upgraded to a newer version of the programming language, we discovered that we had been exploiting a bug as a feature for the life of the program to that point. That made it a fun job in support to explain that one to clients. So while the database __should__ stop such behavior, there is something about this programming language that did things like this. Thankfully, I think that is in the past. However, there are still bits of data that need cleanup.

Since these are data issues from old bugs, I know that development won't put a priority on doing anything on their end, so it falls to support to get clients past things like this. Rather than complain that what should be is not, I choose to be pro-active and fix the issue, make the client happy and move on to the next issue.

I am thankful that this product has a documented data model with great technician documents and I am very familiar with it.

If it was another product I support, I would not be asking for help here. Its data model looks like it does when people first get their hands on MS-Access, "Oooh, I built a data base!" Data repeated in every table instead of relying on the power of relational databases to track it. That nightmare of a database is being migrated from Advantage to MS-SQL without fixing the issues with the data model. There is no documented data model, unless development won't share, and no technician documentation. Every new support tech to this product has to figure it out as they go. Now that is something to complain about. If I didn't need to sleep I would figure out what table changes when I do something on each screen so that anyone could support it. So with problems on that product, I make its programmers answer the questions since I don't have time to figure it out in a time frame that is useful to the client.

Well, enough venting, it doesn't help since management has to give development approval to document the data model, or better yet, do it right, nothing I can do will change that.

Thanks again!

~ Larry