Correct SQL Syntax for sp_send_dbmail

When using sp_send_dbmail what is the correct syntax for the @query.  My sql statment is not working.  However when I comment that section out it performs fine.  Here is my sample @query I think the issue is occurring where I have:

@query='SELECT * from food.LimsErrors WHERE [Column 0] = ''Yellow'''

What should the proper syntax be.  I am guessing it is the number of apostrophes I have?  Please advise.
Gary HarperAsked:
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
You need to put your database name too while specifying the table.
Select * from dbname.dbownr.tablename
I concur with aneeshattingal, you need to put DBName in your query. Here is one example from MSDN.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;
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.

All Courses

From novice to tech pro — start learning today.