Thursday, November 5, 2015

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


No comments: