+ Reply to Thread
Page 4 of 4 FirstFirst ... 2 3 4
Results 31 to 33 of 33

Thread: If statement stored procedure

  1. #31
    Barn Enthusiast peebman2000 is on a distinguished road peebman2000's Avatar
    Join Date
    Mar 2008
    Posts
    210
    Rep Power
    3

    reply

    Yeah, I tried it without the conversion, (see below) and I get no error's, but the email does go NOW. I changed the dates in the 3 records to match today's date and it should have sent the email like it did before. But of course I now selecting the email address and date now.

    But I"m going to put the conversion part back in and try it again. I'm looking on line to see if there is something about that datetime conversion error.

    Any other ideas let me know.

    Code:
    USE [peebman]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
    AS 
    SET NOCOUNT ON 
    declare @birth datetime, @email varchar(150)
    
    declare row cursor for
    select email, birth from email where birth = getdate()
    
    open row
    
    fetch row into @birth, @email
    
    while @@fetch_status = 0
    
    begin
    	fetch row into @birth, @email
    IF  @birth = getdate()
    	
    	Begin
            declare @body1 varchar(100)
    		
            set @body1 = 'You are reminded to finish your PDQ!'---'Server         EXEC msdb.dbo.sp_send_dbmail 
    		
            @recipients = @email,     
            @subject = 'My Mail Test',
            @body = @body1,
            @body_format = 'html';
    	end 
    else if @birth <> getdate()
    BEGIN
    	EXEC msdb.dbo.sysmail_stop_sp	
    	End
    end
    close row
    
    Deallocate row
    
    return
    
    Quote Originally Posted by jmurrayhead View Post
    Not sure what's causing the error all of a sudden. Have you tried it again without the conversion to see if that is actually the issue?

  2. #32
    Barn Enthusiast peebman2000 is on a distinguished road peebman2000's Avatar
    Join Date
    Mar 2008
    Posts
    210
    Rep Power
    3

    reply

    Okay Jmurrayhead, I finally got everything to work. I appreciate the links you sent me they help as well, but it works now. I can schedule for reminder emails to be sent out using sql 2005 job scheduler.

    Here is my final stored procedure:
    Code:
    USE [peebman]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
    AS 
    SET NOCOUNT ON 
    exec msdb.dbo.sysmail_start_sp
    declare @birth varchar(15),
    @email varchar(8000)
    set @email =''
    declare row cursor for
    select email, birth from email where birth = CONVERT(varchar(15), GETDATE(), 101)
    -- = convert(varchar(8), getdate(), 112)
    
    open row
    
    fetch row into @email, @birth
    
    while @@fetch_status = 0
    
    begin
    	fetch row into @email, @birth
    IF @birth = CONVERT(varchar(15), GETDATE(), 101)
    	
    	Begin
            declare @body1 varchar(100)
    		
            set @body1 = 'You are reminded to finish your PDQ!'---'Server 
            EXEC msdb.dbo.sp_send_dbmail 
    		
            @recipients = @email,        --@BCC = @BCCList,
            @subject = 'My Mail Test',
            @body = @body1,
            @body_format = 'html';
    	end 
    else if  @birth <> CONVERT(varchar(15), GETDATE(), 101)
    BEGIN
    	EXEC msdb.dbo.sysmail_stop_sp	
    	End
    end
    close row
    
    Deallocate row
    
    return
    
    I added some more msdb.dbo.sysmail code and I changed the birth(datecolum) to a varchar to get rid of the "Conversion datetime to character string" error.

    And I converted getdate to a varchar to match the birth(datecolumn) in the table.

    That worked as well as the msdb.dbo.sysmail_start_sp code to shoot the email.

    also before in the begin of loop I had:
    Code:
    fetch row into @birth, @email
    
    well the select statment selects email, birth not birth, email so I changed it to:
    Code:
    fetch row into @email, @birth
    
    and i think that helped as well.

    So thinks again for the help, sorry for all the postings. Hopfully this post will help other programmers out who want to sent reminder emails using SQL 2005 instead of using executables on the server.

    Thanks again

  3. #33
    The Barnfather jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead has much to be proud of jmurrayhead's Avatar
    Join Date
    Mar 2008
    Location
    Washington, D.C.
    Posts
    2,347
    Blog Entries
    9
    Rep Power
    19

    Glad you were able to figure out the rest. Don't worry about all the postings, that's what this place is for
    jmurrayhead
    If you agree, give me rep. If my post helped you, click "Thanks".
    If you like it here...throw us a few bones to help support us.


+ Reply to Thread
Page 4 of 4 FirstFirst ... 2 3 4

Similar Threads

  1. Dynamic Stored Procedure
    By jmurrayhead in forum Microsoft SQL Server
    Replies: 16
    Last Post: March 26th, 2008, 11:19 AM
  2. Permissions on Tables, Stored Procedures, etc.
    By theChris in forum Microsoft SQL Server
    Replies: 2
    Last Post: March 24th, 2008, 11:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

SEO by vBSEO