Finding batches with errors across all Dynamics GP company databases

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 the thread.


CREATE TABLE #tempgpquery
(
[DB] VARCHAR(50), 
[Records] INT
)

DECLARE @db_name varchar(10)
DECLARE c_db_names CURSOR FOR
SELECT INTERID FROM DYNAMICS..SY01500

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN
  EXEC('
    INSERT INTO #tempgpquery
    SELECT ''' + @db_name + ''',COUNT(*) FROM ' + @db_name + '..SY00500 WHERE BCHSTTUS > 6
  ')
  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

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, '''');
PRINT @cmd;
EXEC sp_executesql @cmd;';

PRINT @sql;
EXEC sp_executesql @sql;

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com