Running a SQL Script against all GP Company Databases


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

SQLFIX.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

8 thoughts on “Running a SQL Script against all GP Company Databases

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.