[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

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 Harper
Gary Harper
1 Solution
Aneesh RetnakaranDatabase 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 = '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 ;

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now