On Friday I was tasked with putting together a script to email the last dates that our team had synced with Sage ACT! - Admittidly I had put this off until first thing today, mainly due to the issues with PHP not being setup to connect to a MSSQL Database (now resolved, and will become part of another post soon!)
Anyway, the query is as follows:
SELECT DBS.DATABASE_NAME as database_name, convert(varchar,DBMI.[SYNCDBMAPINFO LAST_COMPLETEDATE],120) as last_sync FROM dbo.FNB_SYNCDBMAP() DBM LEFT OUTER JOIN dbo.FNB_SYNCDBMAPINFO() DBMI ON DBM.[SYNCDBMAP SYNCDBMAPID] = DBMI.[SYNCDBMAPINFO SYNCDBMAPID] LEFT OUTER JOIN dbo.SYNC_DATABASE DBS ON DBM.[SYNCDBMAP SUBDBID] = DBS.SYNCDBID ORDER BY DBMI.[SYNCDBMAPINFO LAST_COMPLETEDATE] desc
This provides a list of the Database Name, and the last date of sync. Using PHP I then concatenate these into a HTML table, which then gets sent via email.