coding in a SQL Server job for email

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

coding in a SQL Server job for email

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 13:24:48

I want to do something like this in a SQL Server job:


exec master..xp_sendmail 
	@recipients='brenda@capitaltracer.com', 
	@subject='Bill Due', 
	@message = 'Send out a letter!'
WHERE SendDate = GetDate() FROM tblCapRec

How would I go about doing something like this, since it isn't working right now? Thanks!

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-10 : 13:32:10

>> WHERE SendDate = GetDate() FROM tblCapRec
Besides not being the right syntax for a select and not being possible with a procedure call getdate()is the current datetime including millisecs and is unlikely to match anything stored in a table.
Select all the entries that you want to send into a temp table then loop thruogh it calling xp_sendmail for each entry (getting the values into variables).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 13:39:08

THanks!

So what do I put in the WHERE clause here:

INSERT INTO tblEmail (CaseNumber, PartNumber) SELECT CaseNumber, PartNumber FROM tblCapRec
WHERE PaymentDue =

Thanks!

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 13:50:14

Would this work, or is that a bad way to go about it?

WHERE PaymentDue > getdate() - 1 AND PaymentDue < getdate() + 1

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-10 : 14:00:28

Maybe - depends on what you want.
maybe
PaymentDue > convert)varchar(8),getdate(),112)
for all PaymentDue today (if it doesn't include a time
or
datediff(dd, PaymentDue, getdate()) = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources