Link to home
Start Free TrialLog in
Avatar of Bill Louth
Bill LouthFlag for United States of America

asked on

HowDo I send query results via email


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.
Avatar of dwkor
dwkor
Flag of United States of America image

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';
Avatar of Bill Louth

ASKER

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'.
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
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 ?
ASKER CERTIFIED SOLUTION
Avatar of dwkor
dwkor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nevermind I needed to add Openfire.dbo. to ofMessageArchive

Thanks for your help and quick replies!
Same thing like before - add database and schema name when you reference ofMessageArchive (see my message above - 37024856)
Worked Great!