Running a SQL Script against all GP Company Databases
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.
