By Steve Endow
A customer contacted me with an interesting problem. They utilize Post Master Enterprise
to automatically post batches in over 150 company databases. The automatic batch posting is working fine, but they occasionally have some batches with errors that go into batch recovery. Post Master sends them an email message for each batch that goes into recovery, but with over 150 company databases, they wanted a way to generate a list of all problem batches across all of their company databases.
I haven't done a ton of research into batch recovery and how GP detects which batches to list in the batch recovery window, but based on a quick check of some failed batches in Fabrikam, it looks like the BCHSTTUS field in the SY00500 table was a good place to start.
This KB article lists the different values for the BCHSTTUS field.https://support.microsoft.com/en-us/kb/852420
So now we can create a query like this:
SELECT * FROM TWO..SY00500 WHERE BCHSTTUS > 6
That's a start, but it isn't a great solution if we need to run the query in over 150 different databases.
So after some digging, I found this StackOverflow thread
and used the last suggestion on…