Compartilhar via

Running a SQL Script against all GP Company Databases

  • Artigo

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

  • Anonymous
    October 27, 2008
    Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2008/10/running-sql-script-agains-all-gp.html

  • Anonymous
    October 30, 2008
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/10/developing-for-dynamics-gp-weekly_30.html

  • Anonymous
    November 02, 2008
    Last week, I posted the article below explaining how you can use the osql.exe command to run a script

  • Anonymous
    November 02, 2008
    Last week, I posted the article below explaining how you can use the osql.exe command to run a script

  • Anonymous
    November 04, 2008
    Running a SQL command against all DB's seems to be the topic of the week. Developing for Dynamics

  • Anonymous
    October 26, 2015
    this is the best script ever, it's saved me tons of hours when doing upgrades.  

  • Anonymous
    October 28, 2015
    Hi Rob This script is very handy to keep in your collection. Glad it could help you. David