Link to home
Start Free TrialLog in
Avatar of Gary Harper
Gary HarperFlag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada 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
Avatar of sachitjain
sachitjain
Flag of India image
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 = 'danw@Adventure-Works.com',
    @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 ;