This post demonstrates how you can have T-SQL results being sent to you in emails by using SQL Database Mail.
The article here is assumed that a SQL Database Mail profile "MailProfile1' has been created. The MailProfile1 tells Database Mail what account it should use to send emails. For how to create a Database Mail profile, you can refer to the previous post SQL Database Mail.
To send T-SQL results by emails, run T-SQL sp_send_dbmail as follows as an example.
-- Start T-SQL
USE msdb EXEC sp_send_dbmail @profile_name = 'MailProfile1', @recipients = 'email@example.com', @subject = 'T-SQL Query Result', @body = 'The result from SELECT is appended below.', @execute_query_database = 'msdb', @query = 'SELECT subsystem_id,subsystem FROM syssubsystems'-- End T-SQL --
This example uses the profile MailProfile1 as your email account and sends T-SQL query result to the recipient email firstname.lastname@example.org. The context of email consists of the message @body and the query result. The entire email will look like below.
From: Yuhong Li Sent: Friday, October 29, 2010 2:16 PM To: email@example.com Subject: T-SQL Query Result
The result from SELECT is appended below. subsystem_id subsystem ------------ ---------------------------------------- 2 ActiveScripting 10 ANALYSISCOMMAND 9 ANALYSISQUERY 3 CmdExec 6 Distribution 5 LogReader 7 Merge 12 PowerShell 8 QueueReader 4 Snapshot 11 SSIS 1 TSQL
(12 rows affected)
Here is another example that demonstrates how quotation marks and newlines are used to construct the context of emails.
@subject ='T-SQL PRINT Result',
@body ='The output of PRINT is sent to you as follows.',
@query = "PRINT CHAR(13)+'Use double-quotation marks.' + CHAR(13) + 'Start a newline'"
-- End T-SQL --
The outcome of email will be shown as follows.
From: Yuhong Li Sent: Friday, October 29, 2010 2:16 PM To: firstname.lastname@example.org Subject: T-SQL PRINT Result
The output of PRINT is sent to you as follows.
Use double-quotation marks. Start a newline
For more information about sending emails, see the syntax of sp_send_dbmail in MSDN documents.
<End of Post>
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
I'm trying to execute the script below from SSMS based on your example. I've tried several changes in syntax, but with the script below I get this error:
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '%'.
Can you tell me what I'm doing wrong? Thanx, Wallace
@profile_name = 'whouston',
@recipients = 'email@example.com',
@subject = 'Order Complete, Shipment Pending',
@body = 'Completed Orders with Pending Shipments',
@execute_query_database = 'msdb',
@query = 'use SfiData
select distinct os.OrderNumber, substring(convert(char, cu.customerkey), 1, 7) +
substring(convert(char, cu.CustomerCheckDigit), 1, 1) as Cuskey,
oh.OrderStatus, os.ShipmentStatus, oh.OrderDateTime, os.DeliveryDate, os.DateDescription
from tblOrderShipment as os left outer join tblorderheader as oh
on os.OrderNumber = oh.OrderNumber left outer join tblCustomer as cu
on oh.CustomerNumber = cu.CustomerNumber
where oh.OrderNumber % 100 = (select currentyear from tSystemValues) % 100
and oh.orderstatus like '%Complete%'
and os.ShipmentStatus like '%Pending%'
--and cu.CreditRisk = 0
group by oh.OrderDateTime, os.DeliveryDate, cu.customerkey, os.OrderNumber,
cu.CustomerCheckDigit, oh.OrderStatus, os.DateDescription, os.ShipmentStatus
order by os.OrderNumber
select * from #temp
drop table #temp'
I just resolved it by putting the script in a stored procedure. Thanx!
I have tried that but I am getting the following error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@execute_query_database'.
This is query:
@profile_name = 'SQL Server Alert',
@recipients = 'firstname.lastname@example.org',
@subject = 'ALXN Daily Profile Data Update',
@body = 'NALXN_DailyProfileData file is not available'
@execute_query_database = 'msdb',
@query = 'SELECT * FROM Users where email like ''%Lucas.otero%'''
4 Lucas: Users is a table in msdb database?
Nice article, thanks for sharing
good evening plz send me all ruselt plz my mail I d is email@example.com
Hi can anyone help me ,,,i am trying to execute below script but throwing error saying
"Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid"
@profile_name = 'MailProfile1',
@subject = 'T-SQL Query Result',
@body = 'The result from SELECT is appended below.',
@query = 'SELECT subsystem_id,subsystem FROM syssubsystems'
apperciate yr quick help..
i have a table(tbl_feedback) data will be updated daily and it contains attachments too. i have to get these database for every week with all the attachments. can any one help me
Hello, I want to send query result as a table. How can i implamet html or do you know another way?
I am using SQL2008R2, and I do not have problem to send the database email, my problem is that query result format without linefeed after each row.
So it returns as a long line from header to "----" to results.
How to fix it?
tried and get Mail Queued message .. but did'nt receive recipient email ..
This code gave me exactly what I needed. You rock! Tank Ya!
Very good code, helpful, accurate. Thank you very much.