Bill Louth
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.co
@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_fi
@query_attachment_filename
Thanks in advance.
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'.
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
@query = 'Select Count (conversationID) From MyDatabase.dbo.ofMessageArchive where body like ''?OTR%''',
I assume, of course, that you have table/view named ofMessageArchive
ASKER
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 ?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nevermind I needed to add Openfire.dbo. to ofMessageArchive
Thanks for your help and quick replies!
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)
ASKER
Worked Great!
EXEC msdb.dbo.sp_send_dbmail
@recipients='user@domain.c
@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_fi
@query_attachment_filename