Automatic birthday email send out of MS SQL

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 416 Views - Last Post: 28 July 2014 - 06:27 AM

#1 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 06:35 AM

Good day

I was wondering if it is possible to send out automatic email to people whose birthday it is via the database.

I have a table

Employees

ID
Name
Surname
Birth_date
Email


So the database must have a schedule run at 08:00 am in the morning running through all the database entries in the table Employees and send a email to the individual whose birthday it is saying happy b-day.

Any help will be greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Automatic birthday email send out of MS SQL

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9422
  • View blog
  • Posts: 35,405
  • Joined: 12-June 08

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 07:04 AM

It's not really the database's job to have a scheduled task, right? Why not have a cron job or server task kick off every morning to poll who has a birthday or not that day. A better division of duties.
Was This Post Helpful? 0
  • +
  • -

#3 no2pencil  Icon User is offline

  • Admiral Fancy Pants
  • member icon

Reputation: 5365
  • View blog
  • Posts: 27,328
  • Joined: 10-May 07

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 07:12 AM

Depending on the OS :

Linux : Cron -> php -> mysql
Windows : Task Scheduler -> php -> mysql

As modi123_1 pointed out, it isn't the databases job.

Time Based Daemon, call php engine, pull date values from db, logic to handle dates.

That's how I would approach this project.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5883
  • View blog
  • Posts: 12,765
  • Joined: 16-October 07

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 07:50 AM

The OS would be Windows, for Microsoft SQL Server...

Actually, SQL Server schedules all kinds of tasks. Backup, cleanup, etc. It even sends emails: http://msdn.microsof...y/ms190307.aspx

Also, the job scheduler in MS SQL is superior to the regular old Windows task thingy.

When your job runs, iterate over each entry where birthdate day and month match GetDate day and month. For each hit, fire your email. That's the basics of it.
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9422
  • View blog
  • Posts: 35,405
  • Joined: 12-June 08

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 07:52 AM

Quote

Also, the job scheduler in MS SQL is superior to the regular old Windows task thingy.

My curiosity is now piqued - in what way?
Was This Post Helpful? 0
  • +
  • -

#6 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 07:54 AM

Good day

I am using Microsoft SQL Server Management studio I think I stated that wrong.

This is what I am using with my asp.net application. So the application will not be opened everyday.

So I have no idea how to approach this.

Even if the application is not used everyday the employee still have to get an email saying happy b-day.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5883
  • View blog
  • Posts: 12,765
  • Joined: 16-October 07

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 08:09 AM

View Postmodi123_1, on 09 July 2014 - 10:52 AM, said:

Quote

Also, the job scheduler in MS SQL is superior to the regular old Windows task thingy.

My curiosity is now piqued - in what way?


Heh. It's simply a more complete beastie. A job can have multiple steps. Steps can be jumped to based on failover. There is a built in notification system. The timing granularity ( and this is a big one ) is down to minutes. More here: http://msdn.microsof...y/ms190268.aspx

The Windows Task Scheduler is little more than a pretty front end to the old at job. It will run a single scheduled thing, but the scope of the schedule is rather limited. It's also inferior to a crontab, in this respect. More here: http://msdn.microsof...v=vs.85%29.aspx

View PostThinus du Pisanie, on 09 July 2014 - 10:54 AM, said:

I am using Microsoft SQL Server Management studio I think I stated that wrong.


Yep. That's what you'd use if posting to the MS SQL forum. ;)

View PostThinus du Pisanie, on 09 July 2014 - 10:54 AM, said:

This is what I am using with my asp.net application. So the application will not be opened everyday.


This really has nothing to do with your original question. We're looking to schedule stuff in a database.

View PostThinus du Pisanie, on 09 July 2014 - 10:54 AM, said:

So I have no idea how to approach this.


Start with the above link for SQL Server.

You'll need to write a little T-SQL code to make it happen.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9422
  • View blog
  • Posts: 35,405
  • Joined: 12-June 08

Re: Automatic birthday email send out of MS SQL

Posted 09 July 2014 - 08:15 AM

Meh.. Not overly convinced, but to each their own. I have a host of back-office apps that run at different times through out the day, week, and month. I prefer to keep all my kabodoole in one area.
Was This Post Helpful? 0
  • +
  • -

#9 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Automatic birthday email send out of MS SQL

Posted 21 July 2014 - 01:21 AM

Good day Guys

Sorry have been little busy with other stuff.

Ok I have set up my database mail with gmail account : http://blogs.msdn.co...il-account.aspx

Now I have a database I will attach it if you guys want to have a look at it.

I have read the links you send me. But to be honest I am little clueless with T-SQL have never done it before. :stupid: .

In the database attach you will find there is a extra column name Manager so I want the manager to get a email stating one of your employees name : Thinus bithday is today.

But like I said I have no idea how to do this.

If you will be so kind to help me out will be appreciated.

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5883
  • View blog
  • Posts: 12,765
  • Joined: 16-October 07

Re: Automatic birthday email send out of MS SQL

Posted 21 July 2014 - 04:51 AM

As previously noted, you will have to write a stored procedure that can be fired by a job.

Ok, a quick google got many possibilities for you: http://www.google.co...tored+procedure

That procedure simply needs to select all entries from the table that meet a given criteria and send them email. You'd want a cursor for this...

Actually, with that knowledge, you may amend your google: http://www.google.co...ql+cursor+email

First hit I got seems basic enough: http://sqlmag.com/t-...ustomized-email

Write some code and show where you're stuck.
Was This Post Helpful? 0
  • +
  • -

#11 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Automatic birthday email send out of MS SQL

Posted 22 July 2014 - 04:37 AM

Good day

I have wrote a procedure that will show me the people whose birthday it is but only if it much exactly the date of today.

USE [Customers]
GO
/****** Object:  StoredProcedure [dbo].[EmailData]    Script Date: 2014-07-22 12:59:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE EmailData
 AS
SET NOCOUNT ON
BEGIN
Declare @Date date
SELECT @Date=CAST(GETDATE() as date)
Select 
   a.ID as ID, 
    a.Name as Name,
	a.BirthDate as EmpBirthday, 
    b.Name as Manager, 	
	b.Email as ManEmail
from 
   dbo.Customers  a, 
   dbo.Customers  b 
where 
    a.Manager = b.ID 
	and @Date = a.BirthDate
END
GO

EXEC EmailData


Could you please help me to match the birth date like 1991-07-22 and today's date 2014-07-22 so that the result of the procedure will say Thinus have a birthdate today. So the 07-22 is matching and not the whole 1991-07-22.

If I have that I can start to figure out how to send a automatic email to the manager whose employee has a birth date.

I am a little clueless with store procedures haven't used them for a few years now.

Thanks for help so far.

Regards
Was This Post Helpful? 0
  • +
  • -

#12 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5883
  • View blog
  • Posts: 12,765
  • Joined: 16-October 07

Re: Automatic birthday email send out of MS SQL

Posted 22 July 2014 - 05:47 AM

Right, consider the results of:
select GetDate(), Month(GetDate()), Day(GetDate()), Year(GetDate())



Result:
----------------------- ----------- ----------- -----------
2014-07-22 08:39:17.683 7           22          2014



Given this, you should reasonably be able to do:
select 
		a.ID, a.Name,
		a.BirthDate as EmpBirthday, 
		b.Name as Manager, 	
		b.Email as ManEmail
	from dbo.Customers  a
		inner join dbo.Customers b
			on a.Manager = b.ID
	where Month(a.BirthDate) = Month(GetDate())
		and Day(a.BirthDate) = Day(GetDate())


Was This Post Helpful? 1
  • +
  • -

#13 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Automatic birthday email send out of MS SQL

Posted 22 July 2014 - 05:57 AM

Thanks so much you are a genius.

I will now start to work on using the Email address and sending the information to the manager via email.
Was This Post Helpful? 0
  • +
  • -

#14 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Automatic birthday email send out of MS SQL

Posted 23 July 2014 - 05:39 AM

Ok Cool

I have come up with this. As my Database mailer is already working.

exec msdb.dbo.sp_send_dbmail
@Profile_Name = 'HappyBirthday',
@Recipients = 'aaa@yahoo.com',
@Body = 'One of your employees have a birthday today',
@Subject = 'Happy b-Day';



So this is working I get an email from the database mailer.

Now how do I implement it in the procedure to let it send a email to each manager saying for instance this.

@Proile Name = 'HappyBirthday',
@Recipients = 'MANAGER EMAIL ADDRESS' This comes from the stored procedure,
@Body = 'One of your employees Thinus have a birthday today' The name of the employee also from stored procedure,
@Subject = 'Happy b-Day';

Here is the Procedure that shows me the values. I need to implement it, I just don't know how. :oops:/>/>

USE [Customers]
GO
/****** Object:  StoredProcedure [dbo].[EmailData]    Script Date: 2014-07-23 08:56:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	ALTER PROCEDURE [dbo].[EmailData]

    AS
	SET NOCOUNT ON
	BEGIN
	Declare @Date date

	SELECT GETDATE(), Month(GetDate()), Day(GetDate()), Year(GetDate())
	
	select
	        a.ID, a.Name,	  
	        a.BirthDate as EmpBirthday,
        b.Name as Manager, 
	        b.Email as ManEmail
	    from dbo.Customers  a
	        inner join dbo.Customers b
            on a.Manager = b.ID
    where Month(a.BirthDate) = Month(GetDate())
        and Day(a.BirthDate) = Day(GetDate())	
	
	END
go


Thanks again for assistance so far appreciate.
Was This Post Helpful? 0
  • +
  • -

#15 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5883
  • View blog
  • Posts: 12,765
  • Joined: 16-October 07

Re: Automatic birthday email send out of MS SQL

Posted 23 July 2014 - 11:52 AM

Again, you need to use a cursor.

This is literally the third hit on a google search I already offered: http://www.emailarch.../sql.aspx?cat=6
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2