Find Microsoft Dynamics GP Databases With Workflow Email Actions Enabled

I was doing an upgrade for a client the other day and ran some scripts to remove the web services SQL objects as part of the upgrade (I always find that an upgrade of the Web Services for Microsoft Dynamics GP doesn’t work very well, so it’s a quicker and better option to remove and reinstall.

As part of the reinstall I needed to deploy them to the companies to which they had previously been deployed; I wasn’t sure which companies this was, but I did know that the client had web services for workflow actions enabled in all of them. The script below was my way of finding which databases had the email actions enabled.

The script will is run against the system database and will select from all company databases returning the INTERID and web services server field.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = STUFF((
					SELECT
						CHAR(13)
							+ 'SELECT ''' + INTERID + ''' AS INTERID, Web_Service_Server
							FROM
								' + INTERID + '.dbo.WF00100 AS WF
							WHERE
								EnableWFNotifService = 1'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

EXEC sys.sp_executesql @SQL