Custom jobs help?

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

Based on the power of AI :stuck_out_tongue:

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 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) = ''
DECLARE @subject VARCHAR(100) = 'Email subject'

-- Define SQL query to retrieve data
    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.

can i use a select statement to set @recipients? I would like the job to e-mail variable people depending on certain criteria

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
        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
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 :open_mouth:

Off to go see if it is lost from my lack of attention :frowning:

Thanks for all the info. That’s very helpful

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).

1 Like

So would you build it out on Reporting Services rather than a custom job? or is a custom job still the way to go on this one?

@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.