共用方式為

Running SQL commands against all GP Company Databases

  • 發行項

David Meego

Last week, I posted the article below explaining how you can use the osql.exe command to run a script against all Microsoft Dynamics GP company databases. The method described in this post is best if you have a *.sql script file to execute against each company database:

Running a SQL Script against all GP Company Databases

I have seen some discussions on the newsgroups about the sp_MSforeachdb system stored procedure and so decided to elaborate on this subject.

If you are already logged into SQL Server Query Analyzer or SQL Server Management Studio, you can take advantage of the sp_MSforeachdb system stored procedure. This stored procedure can be used to run a series of SQL commands against every database contained in the current SQL Server instance. However, this will include non Microsoft Dynamics GP company databases as well.

The following example code (provided by Robert Cavill) can be used to check that the current database is actually a Microsoft Dynamics GP company database before executing the commands against it.

Note: As the entire script is contained in quotes, you will need to change any single quotes in your commands to two single quotes. Make sure you include the closing single quote after the end statement.

exec sp_MSforeachdb
' use ?
if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' )
begin
    /* this ia a Great Plains company db */
    print ''Processing database ?''
    /* Insert your script below this Line, Note: Double up single quotes */

    /* Insert your script above this Line, Note: Double up single quotes */
end
'

Yet another method which uses a SQL cursor to run through all the companies is shown in Mariano Gomez's post here.

I hope you find this script useful.

David

Comments

  • Anonymous
    November 02, 2008
    PingBack from http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/27/running-a-sql-script-against-all-gp-company-databases.aspx

  • Anonymous
    November 02, 2008
    David, Very useful and straightforward!   Thanks, Victoria

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

  • Anonymous
    November 04, 2008
    David, It's just a simple and perfect explanation. I had used it once just to test it when I came to know about this proc, but I did it from SQL QA. I did not know whether this could be used outside the SQL QA. Now that clears my ignorance. Thanks Vaidy Mohan

  • Anonymous
    November 06, 2008
    How to grab the data from all the GP databases? like "Select * From Employees".  I am hoping it returns all the employees records from all GP database in ONE table. Thanks

  • Anonymous
    November 06, 2008
    Have a look at the post on Mariano's blog, it obtains all customers from all databases.  You could use very similar code for employees. David

  • Anonymous
    December 02, 2008
    From the Useful SQL Scripts Series . When a system has a batch that is stuck as marked to post or a transaction

  • Anonymous
    April 12, 2011
    I'm using your script to run select statement for all databases but I can't get it to work, my script is as below: SELECT p.PONUMBER, p.POStatus, case When p.POSTATUS = 1 then 'New' When p.POSTATUS = 2 then 'Released' When p.POSTATUS = 3 then 'Change Order' When P.POSTATUS = 4 then 'Received' When P.POSTATUS = 5 then 'Closed' When P.POSTATUS = 6 then 'Cancelled' Else 'Not Valid' end as 'PO_Status', case When p.POSTATUS = 2 then 'Need to Receive' When P.POSTATUS = 4 then 'Invoice' Else 'TBD' end as 'Actions Needed', convert(varchar, p.DOCDATE, 101) as 'PO_Date', p.REMSUBTO, p.SUBTOTAL, p.VENDORID, p.VENDNAME, p.CMPANYID, p.PRSTADCD, p.CMPNYNAM, d.Comment_1 from pop10100 p join pop10550 d with (nolock) on p.ponumber = d.popnumbe where POStatus in (1, 2, 3, 4) ORDER BY POStatus ASC