Run a SQL script across all Dynamics GP company databases

By Steve Endow

This may be posted elsewhere, but I just whipped it up quickly and thought I would post it for posterity.

If you need to run a SQL script across all Dynamics GP company databases, this is one way.  There are likely other ways, but this approach is actually relatively simple compared to the alternatives.

USE DYNAMICS

DECLARE @INTERID varchar(10)

DECLARE INTERID_Cursor CURSOR FOR

SELECT INTERID FROM DYNAMICS..SY01500

OPEN INTERID_Cursor

FETCH INTERID_Cursor INTO @INTERID

WHILE @@Fetch_Status = 0

BEGIN

  EXEC('UPDATE ' + @INTERID + '..SOP10100 SET WorkflowApprStatCreditLm = 9 WHERE BACHNUMB LIKE ''ABC%'' AND WorkflowApprStatCreditLm = 1')

  FETCH INTERID_Cursor INTO @INTERID

END

CLOSE INTERID_Cursor

DEALLOCATE INTERID_Cursor

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