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 (8)

  1. David,

    Very useful and straightforward!  

    Thanks,

    Victoria

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

  3. Vaidy says:

    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

  4. nonetodo says:

    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

  5. David Musgrave says:

    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

  6. From the Useful SQL Scripts Series . When a system has a batch that is stuck as marked to post or a transaction

  7. Ken says:

    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

Skip to main content