While administering a Microsoft Dynamics GP system, there are times when a fix or change needs to be applied to each company in the system at the SQL Server level.  On a system with a large number of companies this can be a very time consuming task.

Wouldn't it be nice if there was some method of automating the process so that a SQL script file could be automatically executed against every company database in an instance of SQL Server without touching databases which are not related Microsoft Dynamics GP.

Well, there is....

A while back, my friend Robert Cavill and I created SQLFIX.BAT batch file which uses the osql.exe command to obtain a list of Microsoft Dynamics GP company databases and then uses it again to execute the supplied *.sql script file against each database.

SQLFIX needs 3 parameters passed to it:

• Parameter 1 - SQL Server Instance Name
• Parameter 2 - 'sa' password for SQL Server
• Parameter 3 - Name of SQL script to execute

Any output messages will be sent to a file called SQLFIX.OUT.

The SQLFIX.BAT script is attached to the bottom of this post. 

Another method of running code against all company database can leverage the sp_MSforeachdb system stored procedure.  See the post below for more information:

Running SQL commands against all GP Company Databases

Note: The method using sp_MSforeachdb requires all single quotes in the commands to be changed to two single quotes.

I hope you find this information useful.

David

03-Nov-2008: Added link to companion article on sp_MSforeachdb stored procedure.