Sending an email with the results from a SQL query using a SmartConnect SQL task takes some setup work in SQL. If configured correctly though, it can be a very useful took when running SmartConnect maps and adding the task.
Here is a video that shows the setup and the steps with considerations that you’ll need to take. The steps and additional considerations can be found below.
Considerations:
- You will need to be an Administrator in MSSQL to set up the Database Mail account that is needed for this process.
Setting up Database Mail
Configuring Database Mail XP’s
- In Microsoft SQL Management Studio, run the SQL scripts below.
- In these scripts, it will be setting it to show the advanced options, enabling the Database Mail XP’s, checking to be sure it has been changed, and hiding the advanced options again.
/* show advanced options */ EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO /* enable Database Mail XPs */ EXEC sp_configure 'Database Mail XPs', 1 GO RECONFIGURE GO /* check if it has been changed */ EXEC sp_configure 'Database Mail XPs' GO /* hide advanced options */ EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE GO
Create Database Mail Profile and Account
- If you already have a default profile and account with Database Mail you can skip these steps.
- In these commands, we will create a profile, create an account, and add them together.
- The only tricky part of this step is finding your mail server name. If you use Microsoft 365, it will be something like the Contoso option seen below, but it will be specific for each company.
- Open SQL Management Studio and copy the scripts below to a new query window.
- Default values have been setup. They can be left as is or changed if you wish.
- In the Create a Profile script:
- @profile_name = ‘Mail Database Profile’,
- @description = ‘Mail Database Profile’;
- In the Create an Account script:
- @account_name = ‘Mail Database Account’,
- @description = ‘Mail Database Account’,
- In the Create a Profile script:
- Replace “Example@email.com” value in the @email_address and @replyto_address in the Create an Account script with the correct recipient email addresses.
- Change the @mailserver_name value to have the correct mailserver for your environment.
- Execute all these SQL scripts.
/* Create a Profile for Mail Database */ EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Mail Database Profile', @description = 'Mail Database Profile'; /* Create an account for Mail Database. */ /* Put in the correct email_address and replyto_address */ /* Verify the mailserver_name as this will be different for your company. */ EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Mail Database Account', @description = 'Mail Database Account', @email_address = 'Example@email.com', @replyto_address = 'Example@email.com', @display_name = 'Mail Database Account', @mailserver_name = 'contoso-com.mail.protection.outlook.com'; /* Add the account and the profile together and gives it a sequence number */ EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Mail Database Profile', @account_name = 'Mail Database Account', @sequence_number = '1';
Setup Email Send Process
- This will use the Profile and Account setup previously.
- In this process you will set the profile you created above, designate the recipient you can add more if you like, then you will have a query you will need to have this in single quotes, you will need to define the database in your select statement like I have below. I also added a TRIM to all the fields so the resulting query appears better.
- Copy the SQL Script below into SQL Server Management Studio
/* This sends an email to the designated person(s). /* In the @query value, if you need to use single quotes, it will need to be two single quotes to note break the string. This can be seen in the sample below. */ /* In the @query value, the database will need to be declared and the database, dbo, and table name will need to be enclosed in square brackets []*/ /* In this example, we have a select from a specific table ([GPTWO].[dbo].[RM00101]). You can select all the values or specify the fields. You can also add where clauses, grouping, etc. */ /* You will need to change the query in the @query value for testing purposes to something that exists in your database */ EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Database Profile', @recipients = 'you.email@example.com', @query = 'SELECT TRIM(CUSTNMBR) AS CUSTNMBR, TRIM(CUSTNAME) AS CUSTNAME, TRIM(CUSTCLAS) AS CUSTCLAS, TRIM(CNTCPRSN) AS CNTCPRSN, TRIM(COUNTRY) AS COUNTRY FROM [GPTWO].[dbo].[RM00101] WHERE TRIM(COUNTRY) = ''Canada''', @query_result_header = 1, @query_result_separator = '|', @subject = 'Sql Query Results', @query_result_no_padding = 1, @query_attachment_filename = 'sql query complete.txt', @exclude_query_output = 1, @attach_query_result_as_file = 1;
- If you change the @profile_name value in the creation of the Profile previously, update that value in the script
- Change you.email@example.com in the @recipients value to a valid email address you wish to send to.
- Update the select statement in the @query value to something relevant to your environment.
- If desired, the @query_result_separator can be changed from the pipe delimited option. The @query_attachment_filename can also be changed to what you would like.
- Once the script is updated how you desire, run the script.
- An email should have been sent to the recipient specified. This is an example of what the results will look like.
Add SQL Script as Task in SmartConnect
- Open the map you wish to add a task to in SmartConnect.
- On your map go to tasks and select the task you want to add the email task to.
- Right click on the ask and select New Task – Run SQL Command., then create a new Run SQL Command Task.
- In the Task: Run SQL Command window, enter in the following information.
- Task Name: This can be what you want to call it.
- Mark the Enable checkbox
- Select your Microsoft SQL Server connection and verify the server is correct.
- Select the proper database.
- In the SQL Command, paste in the modified script that was run in the “Setup Email Send Process”.
- Save the Task and map.
- The next time the integration is run, it would send an email with an attachment of the SQL query.
If you have further questions, feel free to send us an email at support@eonesolutions.com.
