Query run against all DBs for any customer that has PaperSave for Dynamics GP, Financial Edge, Dynamics SL, Intacct, and Dynamics 365 Business Central that also have the auto entry module installed.
Here is the query I have created which will run against all DBs for any customer that has PaperSave for Dynamics GP, Financial Edge, Dynamics SL, Intacct, and Dynamics 365 Business Central that also have the auto entry module installed.
use master
DECLARE @db_name nvarchar(max)
DECLARE @string nvarchar(max)
DECLARE @ID INT
DECLARE @sqlCommand NVARCHAR(4000)
if object_ID(N'tempdb..#report') is not null
begin
drop table #report
end
CREATE TABLE #report (Teanant nvarchar(max),HostaApplicationName nvarchar(max),CompnayName nvarchar(max))
DECLARE c_db_names CURSOR FOR
SELECT name FROM sys.databases
--WHERE name IN ('PaperSaveSP11')
WHERE name not IN('master','model','msdb','tempdb','DBATools','PaperSaveContentSearchTest','PaperSaveGrantEdge','PaperSaveInvoiceReport','PaperSavemylesvcrm','PaperSaveTenants','papersave2') order by name --might need to exclude more dbs
OPEN c_db_names
FETCH c_db_names INTO @db_name WHILE @@Fetch_Status = 0
BEGIN
SET @sqlCommand = 'USE '+@db_name+' ; select @ID=count(ID) from config where ConfigName =''Auto Entry enabled'' and ConfigValue=''yes'''
EXEC sp_executesql @sqlCommand, N'@ID INT OUTPUT',@ID = @ID OUTPUT
--print @ID
if (@ID>0)
begin
EXEC(' INSERT INTO #report(Teanant,HostaApplicationName,CompnayName) select ''' + @db_name + ''',h.Name,c.CompanyName from ' + @db_name + '..hostapplication h inner join ' + @db_name + '..Company c on h.ID=c.HostApplication_ID where Name = ''Dynamics GP'' or Name = ''Blackbaud''''s The Financial Edge'' or Name =''Microsoft Dynamics SL'' or Name = ''Dynamics 365 Business Central'' or Name = ''Intacct''')
--print @string
end
FETCH NEXT FROM c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
SELECT * FROM #report
