Friday, October 2, 2015

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.

So now we can create a query like this:


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)

OPEN c_db_names

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

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.


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 ''
 + '.sys.tables AS t
 INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 WHERE  = N''SY00500''
 AND  = 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

No comments: