Solved

SQL Server 2005 Script

Posted on 2007-03-27
2
248 Views
Last Modified: 2010-03-19
How would you write a SQL Script to check a record for a field called DateStamptxt of type smalldatetime and test --if it is equal to current day or previous day. Then return success/failor to say message should send or not to Database Mail?

Thanks JK

                                                     
0
Comment
Question by:fsuedu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18800271
IF EXISTS (SELECT 1 FROM urTable WHERE DateStampTxt > = CAST(CONVERT(varchar,GETDATE() -2, 112) AS DATETIME) )
 Exec master..xp_SendMail ''
Else
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18800361

Hi refer to the link to configure the database mail
http://www.databasejournal.com/features/mssql/article.php/3626056



IF EXISTS (SELECT 1 FROM urTable WHERE DateStampTxt > = CAST(CONVERT(varchar,GETDATE() -2, 112) AS DATETIME) )
BEGIN
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients=test@yahoo.com',
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;
END
ELSE
BEGIN
----whatever you want vam do here
END


0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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