This article walks through the steps that need to be taken to get a Vendor Change History Report on a daily basis through a maintenance plan email notification.
1. A maintenance plan needs to be setup so that the report could be emailed everyday.
2. Notes about setting up the maintenance plan:
- Setup a profile under database mail in sql. This profile is used as the profile name in the script.
- Make sure a virtual smtp server is setup in IIS on the sql server with Relaying enabled.
- Create a scheduled tasks to start and stop the smtp service if you cannot get rid of errors.
3. The following T-SQL statement was used in the maintenance plan. Below is a breakdown of the script. Make sure to change the names for the text in orange and green! Red text within the script is optional to change.
- Portal's Vendor Management Database (4 location)
- Profile Name
- Portal Framework Database (1 location)
- @recipients: Report Email Recipient(s)
- @subject: Report Email Subject (optional)
- @body: Report Email Content (optional)
@profile_name = 'UnitedFCU',
@recipients = 'firstname.lastname@example.org; email@example.com',
@subject = 'Vendor Change History T-SQL Query Result',
@body = 'The result from SELECT is appended below.',
@execute_query_database = 'VendorManagement',
@query = 'SELECT FirstName + " " + LastName as VendorUser
FROM [VendorManagement].[dbo].[ChangeHistory] INNER JOIN VendorManagement.dbo.Vendors on Vendors.ID = ChangeHistory.VendorID
INNER JOIN PortalFx.dbo.PWUser on PWUser.UserID = ChangeHistory.UserID
where DATE between GetDate()-1 and GetDate() and Vendors.Deleted = 0 and Vendors.IsActive = 1
order by DATE'