ah, looks like this actually runs a query for each DB, rather than one body of data for all companies (unless i'm doing it wrong?). this DQ's this solution for me, but if it's more of a report you are looking for
@Heidi Gibbons, just change your results over to Text, and this script would be super useful.
That being said, I did not know about the DB_Name() function until now, that was what was stopping me from using the multicompany smartlists!! so thanks
@David Morinello !!
------------------------------
Mike Bresnan
Arabella Advisors
Washington MD
------------------------------
Original Message:
Sent: Jul 14, 2022 12:37 PM
From: David Morinello
Subject: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400
It could, but SmartList Builder has an option for a Multicompany SmartList.
Add a calculated field to identify the company.
Make sure your "Maximum records" are high enough to not drop off one or more companies.
If the result set is too large for SmartList, try SSRS.
------------------------------
David Morinello
Senior Developer
TruckPro LLC
Cordova TN
Original Message:
Sent: Jul 14, 2022 09:07 AM
From: Mike Bresnan
Subject: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400
Can that script be leveraged in SmartList builder to become a smartlist/excel report?
I have built a bunch of SQL Union queries where each DB is called out specifically, it would be great to change the code over to this!
------------------------------
Mike Bresnan
Arabella Advisors
Washington MD
Original Message:
Sent: Jul 14, 2022 08:12 AM
From: David Morinello
Subject: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400
Try adding your SQL to this code. It will loop through every GP company database.
DB_name() AS [Database] will include a column with the DB name and change as it loops through the companies.
Replace my Top 50 SQL code with your own.
Important: Double up on any single quotes in your code. Any ' becomes '' (two single quotes.)
----------------------------------------------
-- Top 50 Table Row Counts Each GP Company
-- Recommend Setting Query > Results to File
----------------------------------------------
EXEC sp_MSforeachdb
' use ?
if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' )
begin
/* this is a Great Plains company db */
--print ''Processing database ?''
/* Insert your script below this Line, Note: Double up single quotes */
SELECT TOP 50
@@Servername as [SQL Server]
,DB_name() AS [Database]
,(SCHEMA_NAME(A.schema_id) + ''.'' + A.Name) AS TableName
,SUM(B.rows) AS RecordCount
FROM sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE A.type = ''U''
GROUP BY A.schema_id, A.Name
Order by SUM(B.rows) Desc
/* Insert your script above this Line, Note: Double up single quotes */
end
'
---****************************************************************************************************
------------------------------
David Morinello
Senior Developer
TruckPro LLC
Cordova TN
Original Message:
Sent: Jul 13, 2022 11:07 AM
From: Heidi Gibbons
Subject: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400
I have 80 databases and I am trying to figure out what query to create in order to return the ending balance of all account beginning with 1400 and 2400. I also want it to include the account description and database name. It seems simple enough but I am having issues. I am currently opening every database and running a trial balance with those parameters and it is taking a long time. I do this every quarter so any help would be greatly appreciated. Also, can anyone recommend a good course to take? I have taken SQL classes at the GPUG Conferences but want more knowledge than they have to offer.
------------------------------
Heidi Gibbons
Senior Accountant
Walnut Ridge Strategic Management Company, LLC
Akron OH
------------------------------