Thinus du Pisanie's Profile
Reputation: 2 Apprentice
- Active Members
- Active Posts:
- 106 (0.1 per day)
- 07-October 11
- Profile Views:
- Last Active:
- Today, 04:52 AM
- Dream Kudos:
Posts I've Made
Posted 25 Jul 2014Ok this is what I got
Alter PROCEDURE EmailData AS BEGIN Declare @email nvarchar(128) Declare @Date date SELECT GETDATE(), Month(GetDate()), Day(GetDate()), Year(GetDate()) DECLARE rcpt_cursor CURSOR FOR select b.Email 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()) OPEN rcpt_cursor FETCH NEXT FROM rcpt_cursor INTO @email DECLARE @ServerAddr nvarchar(128) Set @ServerAddr = 'smtp.gmail.com' DECLARE @From nvarchar(128) Set @From = 'My Email Address' DECLARE @Bodytext nvarchar(512) Set @BodyText = 'Your Employees have a birthday today.' DECLARE @User nvarchar(128) Set @User = 'My Email Address' DECLARE @Password nvarchar(128) Set @Password = 'My Password' DECLARE @SSL int Set @SSL = 0 DECLARE @Port int Set @Port = 587 WHILE @@FETCH_STATUS = 0 BEGIN PRINT @email DECLARE @subject nvarchar(255) SELECT @Subject = 'Happy Birthday' + @email EXEC EmailData @ServerAddr, @from, @email, @subject, @BodyText, @User, @Password, @SSL, @Port FETCH NEXT FROM rcpt_cursor INTO @email END CLOSE rcpt_cursor DEALLOCATE rcpt_cursor End Go
When I execute it I get Command(s) completed successfully. But I don't get the emails and I don't understand why. Is there somewhere where there will be a log to show why this is happening?
Posted 23 Jul 2014Ok Cool
I have come up with this. As my Database mailer is already working.
exec msdb.dbo.sp_send_dbmail @Profile_Name = 'HappyBirthday', @Recipients = 'email@example.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. />/>
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.
Posted 22 Jul 2014Thanks 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.
Posted 22 Jul 2014Good 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.
Posted 21 Jul 2014Good 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. .
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.
- Member Title:
- D.I.C Head
- Age Unknown
- Birthday Unknown
- Click here to e-mail me
Thinus du Pisanie hasn't added any friends yet.