Correct SQL Syntax for sp_send_dbmail

Posted on 2012-08-31
Last Modified: 2012-09-03
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.
Question by:Gary Harper
    LVL 75

    Accepted Solution

    You need to put your database name too while specifying the table.
    Select * from dbname.dbownr.tablename
    LVL 12

    Expert Comment

    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 ;

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now