Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

How do i send the query results using SQL 2005 and Database Mail

I have database mail setup and I can send myself a test message in either text or html... but when I try to send a query as the message, the email fails.

This is my test code that works:


DECLARE @Message AS VARCHAR(100)
SET @Message ='Server :' + @@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LOGON',
@subject = 'Testing',
@recipients = 'email address here',
@body_format = 'TEXT',
@body = @message,


This is my testcode that fails

DECLARE @Message AS VARCHAR(100)
SET @Message ='Server :' + @@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LOGON',
@subject = 'Testing',
@recipients = 'email address here',
@body_format = 'TEXT',
@body = @message,
@Query = 'Select * from [Jason_DB].[dbo].[Sales_JobEntry]'

0
logoncom
Asked:
logoncom
  • 4
  • 3
1 Solution
 
chapmandewCommented:
Do you have permission to run the query listed in the stored procedure?
0
 
logoncomAuthor Commented:
I wasnt trying to access a sp, will it only work with sprocs?

Yes, I have full access
0
 
chapmandewCommented:
i meant the msdb.dbo.sp_send_dbmail stored procedure.

Make sure your database mail profile is public.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
logoncomAuthor Commented:
The profile is public.  I can send email... I have already tried... my problem is I do not know how to send query results as the body of the email.
0
 
chapmandewCommented:
You're getting an error when you do, right?  What is the error?
0
 
logoncomAuthor Commented:
I get bad syntax or wrong variable etc... problem is I dont know how to tell database mail to send the results of the qeury as the body of the email and not a message.

How would I email:  Select * from Table where field = 'xyz'
0
 
logoncomAuthor Commented:
In case anyone wants the solution to this... I figured it out myself


--sp_send_dbmail
--    [ [ @profile_name = ] 'profile_name' ]
--    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
--    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
--    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
--    [ , [ @subject = ] 'subject' ]
--    [ , [ @body = ] 'body' ]
--    [ , [ @body_format = ] 'body_format' ]
--    [ , [ @importance = ] 'importance' ]
--    [ , [ @sensitivity = ] 'sensitivity' ]
--    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
--    [ , [ @query = ] 'query' ]
--    [ , [ @execute_query_database = ] 'execute_query_database' ]
--    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
--    [ , [ @query_attachment_filename = ] query_attachment_filename ]
--    [ , [ @query_result_header = ] query_result_header ]
--    [ , [ @query_result_width = ] query_result_width ]
--    [ , [ @query_result_separator = ] 'query_result_separator' ]
--    [ , [ @exclude_query_output = ] exclude_query_output ]
--    [ , [ @append_query_error = ] append_query_error ]
--    [ , [ @query_no_truncate = ] query_no_truncate ]
--    [ , [ @query_result_no_padding = ] query_result_no_padding ]
--    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]


--QUERY EMAIL MESSAGE

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile',
    @recipients = 'recipientsemail@email.com',
    @query = 'SELECT * FROM TableName' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 0 ;


--HTML EMAIL MESSAGE

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;


--send email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AdventureWorks Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now