Need to run a script on 200 databases

[GPNut]

Clients are on SQL2005 GP 9.0. I have created the following script to
run 2 process that will produce results that I can send to excel. But
the client needs this run on around 200 databases does anyone have any
ideas how to make this a shorter process.

--Script for pulling between 9999.00 and 50000.00 from Cash account.

SELECT GL105.ACTNUMBR_2, GL105.ACTNUMBR_3, GL105.ACTNUMBR_4,
GL105.ACTINDX, GLGL2.JRNENTRY, GL2.SOURCDOC, GL2.REFRENCE,
GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES, GL2.CRDTAMNT,
GL2.DOCDATE, GL2.ORMSTRNM
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = '010') AND (GL105.ACTNUMBR_3 = '1040')
AND (GL105.ACTNUMBR_4 = '000') AND (GL2.TRXDATE > '01/1/2008') AND
(GL2.CRDTAMNT < 50000.00 and GL2.CRDTAMNT > '9999.00')

--- Statement to pull in all credit amounts greater than $50,000.00
from cash account

SELECT GL105.ACTNUMST, GL105.ACTNUMBR_2, GL105.ACTNUMBR_3,
GL105.ACTNUMBR_4, GL105.ACTINDX, GL2.JRNENTRY, GL2.SOURCDOC,
GL2.REFRENCE, GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES,
GL2.CRDTAMNT, GL2.DOCDATE
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = '010') AND (GL105.ACTNUMBR_3 = '1040')
AND (GL105.ACTNUMBR_4 = '000') AND (GL2.TRXDATE > '01/1/2008') AND
(GL2.CRDTAMNT > '50000.00')

[Mohammad R. Daoud]

Dear GPNut,

Steps below will help:

1. First define a cursor that loops all the records in “SY01500” table (GP
Databases).

DECLARE CurDatabases Cursor For Select INTERID From SY01500
……
While...
Fetch Next From CurDatabases Into @DatabaseName

2. Change the database used in loop and run its command:
DECLARE @strCommand Varchar(500)
@strCommand = ‘USE ‘ + @DatabaseName
EXEC (@strCommand)
Add your commands here that include the data export.

End While

This what currently came to my mind, please let me know if you need any more
clarifications.

Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Mob: +962 - 79 -999 65 85
Great Package For Business Solutions
dao...@greatpbs.com
http://www.greatpbs.com
http://mohdaoud.blogspot.com/

[David Musgrave [MSFT]]

Have a look at my post on the SQLFIX.BAT utility

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/27/running-a-sql-script-against-all-gp-company-databases.aspx

I posted this only 3 days ago. :-)

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.M...@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

[Mariano Gomez]

declare @companyID char(10)
declare @sqlstmt varchar(100)
declare cursor c_company for select interid from sy01500 where interid <>
'TWO'

open c_company
fetch next from c_company into @companyID

while @@fetch_status = 0
begin

set @sqlstmt = 'use ' + @companyID
exec @sqlstmt

SELECT GL105.ACTNUMBR_2, GL105.ACTNUMBR_3, GL105.ACTNUMBR_4,
GL105.ACTINDX, GLGL2.JRNENTRY, GL2.SOURCDOC, GL2.REFRENCE,
GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES, GL2.CRDTAMNT,
GL2.DOCDATE, GL2.ORMSTRNM
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = '010') AND (GL105.ACTNUMBR_3 = '1040')
AND (GL105.ACTNUMBR_4 = '000') AND (GL2.TRXDATE > '01/1/2008') AND
(GL2.CRDTAMNT < 50000.00 and GL2.CRDTAMNT > '9999.00')

--- Statement to pull in all credit amounts greater than $50,000.00
from cash account

SELECT GL105.ACTNUMST, GL105.ACTNUMBR_2, GL105.ACTNUMBR_3,
GL105.ACTNUMBR_4, GL105.ACTINDX, GL2.JRNENTRY, GL2.SOURCDOC,
GL2.REFRENCE, GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES,
GL2.CRDTAMNT, GL2.DOCDATE
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = '010') AND (GL105.ACTNUMBR_3 = '1040')
AND (GL105.ACTNUMBR_4 = '000') AND (GL2.TRXDATE > '01/1/2008') AND
(GL2.CRDTAMNT > '50000.00')

fetch next from c_company into @companyID
close c_company
deallocate c_company

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

[GPNut]

On Oct 31, 8:01 am, Mariano Gomez

> Maximum Global Business, LLChttp://www.maximumglobalbusiness.com
> The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com

> > (GL2.CRDTAMNT > '50000.00')- Hide quoted text -
>
> - Show quoted text -

You are all sweethearts...
Thank you so much ..... I learn more from this site, David, Leslie and
Mariano then I have learned from all my other resources

[George Schimenti]

This might be easier:

There is an unsupported Microsoft SQL System Stored Procedure called
SP_MSForEachDB. You can run your code through that.

SP_MSForEachDB 'SELECT GL105.ACTNUMBR_2, GL105.ACTNUMBR_3,

GL105.ACTNUMBR_4,
GL105.ACTINDX, GLGL2.JRNENTRY, GL2.SOURCDOC, GL2.REFRENCE,
GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES, GL2.CRDTAMNT,
GL2.DOCDATE, GL2.ORMSTRNM
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = "010") AND (GL105.ACTNUMBR_3 = "1040")
AND (GL105.ACTNUMBR_4 = "000") AND (GL2.TRXDATE "01/1/2008") AND
(GL2.CRDTAMNT < 50000.00 and GL2.CRDTAMNT "9999.00")

--- Statement to pull in all credit amounts greater than $50,000.00
from cash account

SELECT GL105.ACTNUMST, GL105.ACTNUMBR_2, GL105.ACTNUMBR_3,
GL105.ACTNUMBR_4, GL105.ACTINDX, GL2.JRNENTRY, GL2.SOURCDOC,
GL2.REFRENCE, GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES,
GL2.CRDTAMNT, GL2.DOCDATE
FROM GL20000 AS GL2 INNER JOIN
GL00105 AS GL105 ON GL2.ACTINDX = GL105.ACTINDX
WHERE (GL105.ACTNUMBR_2 = "010") AND (GL105.ACTNUMBR_3 = "1040")
AND (GL105.ACTNUMBR_4 = "000") AND (GL2.TRXDATE "01/1/2008") AND

(GL2.CRDTAMNT "50000.00")'

I have not validated your SQL but I have used this quite a bit and works
great! I hope that helps.

[GPNut]

On Oct 31, 11:36 am, George Schimenti

> > Mariano then I have learned from all my other resources- Hide quoted text -

>
> - Show quoted text -

I am now receiving the following error

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'cursor'.
Msg 170, Level 15, State 1, Line 31
Line 31: Incorrect syntax near 'c_company'.
I attempted to run this on Master database and test database both

[Mariano Gomez]

Are you passing in the complete SQL statement that I posted for you? If so,
this would explain why you are getting this error message. Just pass in the
original script you created.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

"GPNut" wrote:

> > > > > --Script for pulling between 9999.00 and 50000.00 from Cash account..

> >
> > > > > SELECT GL105.ACTNUMBR_2, GL105.ACTNUMBR_3, GL105.ACTNUMBR_4,
> > > > > GL105.ACTINDX, GLGL2.JRNENTRY, GL2.SOURCDOC, GL2.REFRENCE,
> > > > > GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES, GL2.CRDTAMNT,
> > > > > GL2.DOCDATE, GL2.ORMSTRNM
> > > > > FROM GL20000 AS GL2 INNER JOIN

> > > > > GL00105 AS GL105 ON GL2..ACTINDX = GL105.ACTINDX

> > > > > WHERE (GL105.ACTNUMBR_2 = '010') AND (GL105.ACTNUMBR_3 = '1040')
> > > > > AND (GL105.ACTNUMBR_4 = '000') AND (GL2.TRXDATE > '01/1/2008') AND
> > > > > (GL2.CRDTAMNT < 50000.00 and GL2.CRDTAMNT > '9999.00')
> >
> > > > > --- Statement to pull in all credit amounts greater than $50,000.00
> > > > > from cash account
> >
> > > > > SELECT GL105.ACTNUMST, GL105.ACTNUMBR_2, GL105.ACTNUMBR_3,
> > > > > GL105.ACTNUMBR_4, GL105.ACTINDX, GL2.JRNENTRY, GL2.SOURCDOC,
> > > > > GL2.REFRENCE, GL2.DSCRIPTN, GL2.TRXDATE, GL2.ACTINDX, GL2.SERIES,
> > > > > GL2.CRDTAMNT, GL2.DOCDATE
> > > > > FROM GL20000 AS GL2 INNER JOIN

> > > > > GL00105 AS GL105 ON GL2..ACTINDX = GL105.ACTINDX

[GPNut]

On Nov 1, 10:49 am, Mariano Gomez

<MarianoGo...@discussions.microsoft.com> wrote:
> Are you passing in the complete SQL statement that I posted for you? If so,
> this would explain why you are getting this error message. Just pass in the
> original script you created.
>
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP

> > I attempted to run this on Master database and test database both- Hide quoted text -

>
> - Show quoted text -

If i paste in my SQL Statement I will receive the same results, where
do i enter the script that runs on all companies

[David Musgrave [MSFT]]

The sp_MSforeachdb stored procedure can work if you have SQL commands to run, but you will have to replace all single quotes with
two single quotes and need to adjust the script to make sure it only runs on GP Company database. Please see my new post on the
subject:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/11/03/running-sql-commands-against-all-gp-company-
databases.aspx

If you have a *.sql file, the method in my first post is probably better:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/27/running-a-sql-script-against-all-gp-company-databases.aspx