Thinus du Pisanie's Profile User Rating: -----

Reputation: 2 Apprentice
Group:
Active Members
Active Posts:
107 (0.1 per day)
Joined:
07-October 11
Profile Views:
3,035
Last Active:
User is offline Jul 28 2014 06:25 AM
Currently:
Offline

Previous Fields

Dream Kudos:
0
Icon   Thinus du Pisanie has not set their status

Posts I've Made

  1. In Topic: Automatic birthday email send out of MS SQL

    Posted 28 Jul 2014

    Ok i figured it out :donatello:

    USE [Customers]
    GO
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    ALTER PROCEDURE [dbo].[EmailData]
    
    AS
    
    Declare @email        nvarchar(128)
    Declare @name         nvarchar(128)
    Declare @Date         date
    
    SELECT GETDATE(), Month(GetDate()), Day(GetDate()), Year(GetDate())
    
    DECLARE rcpt_cursor CURSOR FOR 
    select
           a.Name,
           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 @name,@email
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    PRINT @email
    Declare @subject nvarchar(255)
    Declare @Bodytext nvarchar(512)
    
    Set @BodyText = @Name + '' + ' has a birthday today. Please congrad him'
    Set @Subject = 'Employee Birthday' 
    
    exec msdb.dbo.sp_send_dbmail
    @Profile_Name = 'HappyBirthday',
    @Recipients = @email,
    @Body = @BodyText,
    @Subject = @Subject
    
    FETCH NEXT FROM rcpt_cursor
    INTO @name,@email
    
    
    END 
    
    CLOSE rcpt_cursor
    
    DEALLOCATE rcpt_cursor
    
    Go 
    
    Exec [EmailData]
    
  2. In Topic: Automatic birthday email send out of MS SQL

    Posted 25 Jul 2014

    Ok 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?
  3. In Topic: Automatic birthday email send out of MS SQL

    Posted 23 Jul 2014

    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.
  4. In Topic: Automatic birthday email send out of MS SQL

    Posted 22 Jul 2014

    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.
  5. In Topic: Automatic birthday email send out of MS SQL

    Posted 22 Jul 2014

    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

My Information

Member Title:
D.I.C Head
Age:
Age Unknown
Birthday:
Birthday Unknown
Gender:

Contact Information

E-mail:
Click here to e-mail me

Friends

Thinus du Pisanie hasn't added any friends yet.

Comments

Thinus du Pisanie has no profile comments yet. Why not say hello?