Jump to content
Software FX Community

Sums from multiple databases...


eenlow
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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' 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...