Hi I’m new to making custom jobs, and I’m not sure if there’s another way of doing this. But we would like to send an automated e-mail to Polaris staff users who have made obvious errors while creating a new patron to get them fixed. In this case, ones who have input the address without any numerics in them. I was thinking the job could be scheduled every week to go out.
I already have the SQL query to find these specific records, but how do I make a job send an e-mail to a specific Polaris staff user
To use SQL to send emails, you will need to configure the database server to enable SMTP (Simple Mail Transfer Protocol) email connectivity. This can be done by setting up Database Mail or configuring Database Engine Mail in SQL Server.
Once you have set up the database to send emails, you can use SQL queries to send emails to specific users based on their email addresses stored in the database.
In the below example, you would replace user@example.com with the email address of the user you want to send the email to. The SQL query specified in the @query parameter will retrieve data from the myTable table where the myColumn column has a value of 'myValue'.
The query results will be included in the body of the email in a table format. The other parameters in the stored procedure are used to specify the format of the query results. For example, @query_attachment_filename is used to specify the filename of the attached query result file, @query_result_separator is used to specify the separator character used in the query result, and @query_result_header is used to include the column headers in the query result.
-- Set email parameters
DECLARE @recipients VARCHAR(100) = 'user@example.com'
DECLARE @subject VARCHAR(100) = 'Email subject'
-- Define SQL query to retrieve data
DECLARE @query NVARCHAR(MAX) = N'
SELECT *
FROM myTable
WHERE myColumn = ''myValue''
'
-- Send email with query results in table format
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@query = @query,
@attach_query_result_as_file = 0,
@query_attachment_filename = 'query_result.csv',
@query_result_separator = ',',
@query_result_no_padding = 1,
@query_result_header = 1;
You could also take advantage of the default parameter values for the parameters, such as @attach_query_result_as_file , @query_attachment_filename , @query_result_separator , @query_result_no_padding , and @query_result_header by simply omitting them from the EXEC but remember final ; is still required to properly end the EXEC statement.
Yes, you can use a select statement to set the value of @recipients parameter. You can use the STUFF function to concatenate multiple email addresses into a single string separated by semicolons. Here’s an example:
-- Define the criteria for selecting recipients
DECLARE @criteria NVARCHAR(MAX) = 'Sales'
-- Set the email parameters
DECLARE @subject VARCHAR(100) = 'Email subject'
-- Construct the query to select email addresses based on criteria
DECLARE @query NVARCHAR(MAX) = N'
SELECT STUFF((
SELECT ';' + email
FROM users
WHERE department = @criteria
FOR XML PATH ('')
), 1, 1, '''') AS recipients
'
-- Execute the query to select email addresses and set the recipients parameter
DECLARE @recipients VARCHAR(MAX)
EXEC sp_executesql @query, N'@criteria NVARCHAR(MAX)', @criteria, @recipients = @recipients OUTPUT
-- Send the email to the selected recipients
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject;
In this example, the @criteria parameter is used to select email addresses from the users table based on the department column. The STUFF function is used to concatenate the email addresses into a single string separated by semicolons. The @query parameter is constructed to execute this selection, and the sp_executesql stored procedure is used to execute the query and set the value of @recipients . Finally, the @recipients value is passed to the @recipients parameter of the sp_send_dbmail stored procedure to send the email.
You will have to mix/match the SQL to meet your needs based on your SQL that finds the bad entries. I have no idea what you SQL looks like but I assume it has the users email address…
I would have to dig into the code from years ago but I recall writing a trigger that would do this right after a record was saved with bad values… heck now that I think of it, I wonder if that thing is still running
Off to go see if it is lost from my lack of attention
Another option that might be worth considering is setting this up as a scheduled SSRS report. However, I’m most used to SSRS report subscriptions being for one person’s email address.
I think though that @TrevorD did a presentation on dynamic SSRS subscriber lists. Am I remembering that right @TrevorD ? Do you think that would work in this scenario?
Yes! But data driven subscriptions (the feature to let you use SQL to power a sub) is only available with more expensive versions of SQL Server, unfortunately. Unless you have Enterprise (or Developer), then you’ll have to find a solution other than DDS.
If I was setting up something like this in my current consortium, it would probably be a report that was sent on a regular basis and staff would be expected to check/fix any patrons that showed up (although the report in the email would be empty if there was nothing to report so staff could just delete it).
@adantes , I would agree with @TrevorD, you should use SSRS to build the report and then have the report details emailed if desired, but the report could be run by supervisors at any time to see what corrections are needed.
My recommendation is a report, not a SQL job. That’s partially because I have the bias that reports are more accessible to more Polaris customers that custom SQL jobs are (regardless of hosted or not). I also haven’t done any custom SQL jobs yet and I’m 10+ years a Polaris admin. I know that they give you a lot of power and flexibility, but my early admin years were strongly affected by predecessors who cobbled things together that left the organization negatively affected when they left. It is something I’m working on!
Yeah that’s the same thing with me. I have a bunch of broken stored procedures that my predecessor left me and I’m new to Polaris, so I’m trying to pick up the pieces in the best way possible that will be sustainable in the future
When scheduling a custom report, SSRS creates the custom job to handle the execution. Using SSRS is the best approach as anyone in the future can modify the steps using the UI versus trying to unravel custom code at the server level.
