?
Solved

HowDo I send query results via email

Posted on 2011-10-25
9
Medium Priority
?
484 Views
Last Modified: 2012-05-12

Im Trying to have a query run daily and email results.

If I run "Select Count (conversationID) From ofMessageArchive where body like '?OTR%'"

It runs fine but when I have to use that query for email I have to put it as a string like this
'Select Count (conversationID) From ofMessageArchive where body like '?OTR%'',

I have tried double ' or none around the "?OTR%" But it errors out

EXEC msdb.dbo.sp_send_dbmail
@recipients=user@domain.com,
@body= 'Message Body',
@subject ='OTR Records Report',
@profile_name ='Email Main',
@query = 'Select Count (conversationID) From ofMessageArchive where body like '?OTR%'',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt';

Thanks in advance.
0
Comment
Question by:Bill
  • 5
  • 4
9 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 37024733
You need to use double ' characters and also provide @recipient parameter as the string


EXEC msdb.dbo.sp_send_dbmail
@recipients='user@domain.com',
@body= 'Message Body',
@subject ='OTR Records Report',
@profile_name ='Email Main',
@query = 'Select Count (conversationID) From ofMessageArchive where body like ''?OTR%''',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt';
0
 
LVL 8

Author Comment

by:Bill
ID: 37024806
thanks for the quick reply.

I took out the email address for this post. I tried the new query with no sucess.

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: ¿Msg 208, Level 16, State 1, Server SQL2005, Line 1
Invalid object name 'ofMessageArchive'.
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37024856
The query would be executed in the master db context. use database and schema name in the query when you reference the table/view. Something like:

@query = 'Select Count (conversationID) From MyDatabase.dbo.ofMessageArchive where body like ''?OTR%''',

I assume, of course, that you have table/view named ofMessageArchive
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 8

Author Comment

by:Bill
ID: 37024892
Yes the above works. Thanks!

I really want an email every day that prints yes or no is OTR records are found.

IF (Select Count(conversationID) From ofMessageArchive where body like '?OTR%') = 0
Begin
      Print 'No OTR Record Found'
End
Else
      Print 'OTR Records Found!'

The following works just fine but how do I get that to be emailed ?
0
 
LVL 13

Accepted Solution

by:
dwkor earned 2000 total points
ID: 37024953
Something like that:
@query = N'SELECT
      CASE
            WHEN exists (select * from ofMessageArchive where body like ''?OTR%'')
            THEN ''OTR Record Found''
            ELSE ''No OTR Record Found''
      END as Result',
0
 
LVL 8

Assisted Solution

by:Bill
Bill earned 0 total points
ID: 37024998
EXEC msdb.dbo.sp_send_dbmail
@recipients='email goes here',
@body= 'Message Body',
@subject ='OTR Records Report',
@profile_name ='Email Main',
@query = N'SELECT
      CASE
            WHEN exists (select * from ofMessageArchive where body like ''?OTR%'')
            THEN ''OTR Record Found''
            ELSE ''No OTR Record Found''
      END as Result',  
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt';

I get

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: ¿Msg 208, Level 16, State 1, Server SQL2005, Line 1
Invalid object name 'ofMessageArchive'.
0
 
LVL 8

Author Comment

by:Bill
ID: 37025019
Nevermind I needed to add Openfire.dbo. to ofMessageArchive

Thanks for your help and quick replies!
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37025024
Same thing like before - add database and schema name when you reference ofMessageArchive (see my message above - 37024856)
0
 
LVL 8

Author Closing Comment

by:Bill
ID: 37052217
Worked Great!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question