Sums from multiple databases...

[eenlow]

I am trying to create a Cash meter for my company. In my company we have 4 Great Plains DBs. I want to total a field from each database. At this pont i have created a stored procedure for one company get the sum i need. Now my problem is getting the other companies to total with this one. My real problem is im not a pro with SQL nor PowerShell. If possible could someone give me a hand in accomplishing this goal.

 

Thanks

Eric

[marco.shaw]

So you've managed to create a stored procedure that you can use to get the value from each database?

From there, since you're posting here, I'll assume you have PowerGadgets installed.

You can run a stored procedure easily from PowerShell:

PSH>invoke-sql -server SQLSERVER -sql "exec stored_procedure"

(ignoring authentication for now unless you need it)

I'm not sure what kind of information your stored procedure will output, so you'll want to do something like this:

PSH>$sql_var1=invoke-sql -server SQLSERVER -sql "exec stored_procedure"

Then:

PSH>$sql_var1|get-member

You'll need to post here what the above output gives.  We need to get just the string value of your total, and the above is a first step.

 

[eenlow]

Marco,

Thanks for the response. I am actually stuck with writing the SP. This is what i have so for and need to include the other DBs...

This gives me the 1 company. But im not sure how to write the rest.

 

USE [MSI]

GO

GO

/****** Object: StoredProcedure [dbo].[Gadget_MSI_CASH] Script Date: 06/06/2007 10:46:34 ******/

SET ANSI_NULLS ON

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[Gadget_MSI_CASH]

PROCEDURE [dbo].[Gadget_MSI_CASH]

AS

SELECT CM00500.CHEKBKID, CM00500.DSCRIPTN, CM00500.INACTIVE, CM00500.CURRBLNC, CM00500.BANKID, CM00500.Last_Reconciled_Balance,

CM00500.Last_Reconciled_Date, SY04100.BANKNAME, SY04100.CITY, SY04100.STATE

CM00500.CHEKBKID, CM00500.DSCRIPTN, CM00500.INACTIVE, CM00500.CURRBLNC, CM00500.BANKID, CM00500.Last_Reconciled_Balance,

CM00500.Last_Reconciled_Date, SY04100.BANKNAME, SY04100.CITY, SY04100.STATE

.Last_Reconciled_Date, SY04100.BANKNAME, SY04100.CITY, SY04100.STATE

FROM CM00500 INNER JOIN

CM00500 INNER JOIN

SY04100 ON CM00500.BANKID = SY04100.BANKID

ON CM00500.BANKID = SY04100.BANKID

WHERE (CM00500.INACTIVE = 0) and NOT CHEKBKID = 'PNC CREDIT LINE' 

(CM00500.INACTIVE = 0) and NOT CHEKBKID = 'PNC CREDIT LINE' 

[JuanC]

We have a dummy stored procedure that takes 2 parameters and returns the records for which a particular column is between those 2 parameters. I started doing the following

PS C:\Temp> $data = invoke-sql -server myserver -database testdb -sql "execute spGetData1 42,48"PS C:\Temp> $data ProductA : BValue1 : 45Date : 11/10/2006 12:00:00 AMValue2 : 256677Quarter  : Q1 PS D:\Dev\PowerGadgets\Release\Bin\Debug> $data | get-member TypeName: System.Data.DataRow Name   MemberType   Definition----   ----------   ----------AcceptChanges Method   System.Void AcceptChanges()... (Other DataRow members here)Date   Property   System.DateTime Date {get;set;}ProductA   Property   System.String ProductA {get;set;}Quarter Property   System.String Quarter {get;set;}Value1   Property   System.Int32 Value1 {get;set;}Value2   Property   System.Int32 Value2 {get;set;}

Note that each of the columns the SP returns are surfaced as properties, this means you can now write a script as follows

$data1 = invoke-sql -server myserver1 -database testdb -sql "execute spGetData1 42,48"$data2 = invoke-sql -server myserver2 -database testdb -sql "execute spGetData1 42,48"$data3 = invoke-sql -server myserver3 -database testdb -sql "execute spGetData1 42,48"$data4 = invoke-sql -server myserver4 -database testdb -sql "execute spGetData1 42,48"$data1.Value1 + $data2.Value1 + $data3.Value1 + $data4.Value1

If you name this script SumMultipleDB.ps1 you can now invoke it from powershell

PS C:\Temp> .\SumMultipleDB.ps1 | out-gauge

If you are using PowerGadgets creator you can create a gadget with a gauge, select PowerShell as your data Source and either paste the contents of the script or the fully qualified name of the PS1 if you want to reuse the script somewhere else.

JuanC

[marco.shaw]

eenlow,

So you are trying to just write *one* stored procedure?  Am I assuming too much when thinking that you could just write 3 or 4 SPs, and have each one query the proper DB?  We could then combine the results after...