SQL JOB/Status Field - SQL Server Forums

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

Transact-SQL (2000)

SQL JOB/Status Field

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-27 : 15:26:22

I have a SQL job and what it does is it checks every 10 mins to see if there was a new customer added in the table and if there is it sends an email. The problem is that I am using
CONVERT(char(10),date_time, 1) = CONVERT(char(10), GETDATE(), 1)) >0
and whenever there is a new entry added it sends an email so that works great but the drawback is that it sends an email every 10 minutes(that's how I have set up in the SQL job) if there is a new customer added for that day

For example if I add a customer today May 27th it will send me an email every 10 minutes that there is a new customer added and I do want to check every 10 mins to see if there is a new custome added on May 27th but if there is not a new customer I dont want an email.
I beleive I can use status field in my table and set it to 0 as default and once the email get sent it will update it to 1.

BUT I DONT KNOW THE SYNTAX and how to implement it. Any help would be appreciated

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-05-27 : 15:37:59

1.Add a col and in your procedure update the new col with a flag that an e-mail has been sent for the customer
or
2.You can remove him/her from the tbl after you've sent an email
or
3. Add a supporting table - everytime you email a client add him/her to the new tbl and before you send out an e-mail about a new client check if he/she doesn't exist in your "Already Sent" table. Truncate the table every day at midnight

You can't teach an old mouse new clicks.

tradingpassion
Starting Member

24 Posts

Posted - 2005-05-27 : 15:50:48

Thanks for your suggestions!
OK, I like the first part "Add a col and in your procedure update the new col with a flag that an e-mail has been sent for the customer"

2nd and 3rd might not work because I still want to keep those records in the table for 7 days just incase if I dont get an email I can still send it manually

Can you please help me implementing how the first one works?? How does flag works and all that. Thanks in advance

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-05-27 : 19:50:11

I'm not sure what your proc or table looks like. I'll use the following as an example

Table: New_Clients
Cols: Client_ID Int
Client_Name Varchar(50)
Company_Name Varchar(50)
Date_Added Datetime

Add the following

Alter table New_Clients
add Mail_Sent tinyint

As part of your Mail Sending Proc

Update New_Clients
Set Mail_Sent = 1
       from New_Clients
       Where Client_ID = @Client_ID  --(Your Identifie)

The next time your Mail_Proc runs
Include the Following


Select *
from New_Clients
Where Mail_Sent = '0'

Then it will select only the NEW Clients (Clients added since last update)

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources