Update Web Services Server Location in All Companies in Microsoft Dynamics GP

I have a few clients with many company in Microsoft Dynamics GP (and one with well over 100) so doing upgrades or live to test backups can require a fair few, potentially time consuming, changes to data. I’ve posted scripts to update email addresses on test in bulk before as well as a few other variations. One recent one which has come up a couple of times, is the web services server location.

In the Workflow Setup window (Administration area page » Setup » System » Workflow Setup) is a field for the Web Services Server Location; this is the server where the web services have been installed and will be different for a standalone test server to live. Rather than have to move between several dozen companies changing this setting one at a time, the following script can be run to make the change in all companies.

The first highlighted section is the new server location and the second the current one:

/*
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) 
							+ 'UPDATE
								WF
							SET
								Web_Service_Server = ''test.example.co.uk''
							FROM
								' + INTERID + '.dbo.WF00100 AS WF
							WHERE
								Web_Service_Server = ''live.example.co.uk'''
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

EXEC sys.sp_executesql @SQL

The second element is there to make sure the script is only run in companies in which the workflow setup has been completed.