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.
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 the thread.
CREATE TABLE #tempgpquery
DECLARE @db_name varchar(10)
DECLARE c_db_names CURSOR FOR
SELECT INTERID FROM DYNAMICS..SY01500
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
INSERT INTO #tempgpquery
SELECT ''' + @db_name + ''',COUNT(*) FROM ' + @db_name + '..SY00500 WHERE BCHSTTUS > 6
FETCH c_db_names INTO @db_name
SELECT * FROM #tempgpquery
DROP TABLE #tempgpquery
It looks complex due to the temp table and cursor, but it's actually a fairly straightforward query.
You can modify the query to do whatever you need, and in this case it just does a count of records in SY00500 where the batch status is greater than 6 (the red text). I queried the list of valid company database names from the SY01500 table, and use that list in a cursor to loop through each database and query it.
It seems to work very well.
But like I said, I'm not 100% sure if the Batch Status field is the only indicator of batch recovery, so if anyone has more info on the query to properly detect batches that have gone to recovery, please let me know.
UPDATE: The very clever Tim Wappat took up the challenge to find a simpler and cleaner way to perform the query. He uses the novel approach of building a UNION ALL statement that is replicated through a join against sys.databases. The results are the same, but his query avoids the use of both the temp table and cursor. It is rather compact, which makes it a little more difficult to decipher, but it is pretty elegant. For his superior submission, Tim wins 100 Internet Points.
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DECLARE @cmd NVARCHAR(MAX); SET @cmd = N'''';';
SELECT @sql = @sql + N'SELECT @cmd = @cmd + N''UNION ALL
SELECT ''''' + QUOTENAME(name) + ''''', COUNT(*) FROM '
+ QUOTENAME(name) + '.dbo.SY00500 WHERE BCHSTTUS > 6 ''
WHERE EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
+ '.sys.tables AS t
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N''SY00500''
AND s.name = N''dbo'');'
FROM sys.databases WHERE database_id > 4 AND state = 0;
SET @sql = @sql + N';
SET @cmd = STUFF(@cmd, 1, 10, '''');
EXEC sp_executesql @cmd;';
EXEC sp_executesql @sql;