Solved

problem with query syntax of EXEC msdb.dbo.sp_send_dbmail

Posted on 2008-06-25
5
1,840 Views
Last Modified: 2012-05-05
I have a script that alerts me if new records have been added to a table within the past 24 hours:

if not exists (select top 1 * from dbo.mytable where datediff(ss,UpdateDate,getdate()) < 86400)      
      begin
            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'myemail@email.comt',            
            @subject = 'Database alert: No new Data within past 24 hours!',            
            @importance = 'high';
      end

While this works great, I'd really like to include the date last updated in the body of the message, something like: SELECT top 1 'last updated: ', UpdateDate from dbo.mytable order by UpdateDate desc

I tried setting this equal to the @query parameter as such:
@query = 'select top 1 'last updated: ',UpdateDate from dbo.mytable order by UpdateDate desc',

...But i see the error: 'Incorrect syntax near 'last'.  I also tried escaping the ' by using two single quotes on either side of last updated (@query = 'select top 1 ''last updated: '',UpdateDate from dbo.mytable order by UpdateDate desc)

..But I see the error: 'Error formatting query, probably invalid parameters'.

Does anyone know how to fix this?

Thanks in advance.



0
Comment
Question by:hennessym
  • 3
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
Comment Utility
try this:

@query = 'select top 1 ''LastUpdated: '' + CONVERT(VARCHAR(10), UpdateDate, 101) from dbo.mytable order by UpdateDate desc',
0
 
LVL 1

Author Comment

by:hennessym
Comment Utility
Thanks for the quick response, chap!

I tried your suggestion but am still seeing 'Error formatting query, probably invalid parameters'
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
OK, try it without the text to see if it will send the query out:

@query = 'select top 1 UpdateDate, from dbo.mytable order by UpdateDate desc',
0
 
LVL 1

Author Comment

by:hennessym
Comment Utility
Thanks Chap - that revealed that SQL Server was looking for dbo.mytable in the msdb database where the msdb.dbo.sp_send_dbmail resides.  Once I specified the database, the stored procedure successfully executed.

Now, however, I'm seeing the number of rows affected despite having SET NOCOUNT ON as the first line of code in my stored procedure, e.g.:

ALTER procedure [dbo].[NewDataCheck] AS

SET NOCOUNT ON

Any ideas?

0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Thats a new question...one where you'll need to post your procedure code.  
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

10 Experts available now in Live!

Get 1:1 Help Now