Running a SQL Script against all GP Company Databases


David MeegoWhile 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.

SQLFIX.zip

Comments (7)

  1. Last week, I posted the article below explaining how you can use the osql.exe command to run a script

  2. Last week, I posted the article below explaining how you can use the osql.exe command to run a script

  3. Running a SQL command against all DB's seems to be the topic of the week. Developing for Dynamics

  4. Rob Klaproth says:

    this is the best script ever, it's saved me tons of hours when doing upgrades.  

  5. Hi Rob

    This script is very handy to keep in your collection. Glad it could help you.

    David

Skip to main content