DeveloperBarn

peebman2000 · #31 May 23rd, 2008, 09:25 AM

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

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?

peebman2000 · #32 May 23rd, 2008, 03:15 PM

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

jmurrayhead · #33 May 23rd, 2008, 03:54 PM

Glad you were able to figure out the rest. Don't worry about all the postings, that's what this place is for

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html
Posted By	For	Type	Date
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 23rd, 2008 08:19 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 17th, 2008 05:14 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 16th, 2008 06:53 AM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dynamic Stored Procedure	jmurrayhead	Microsoft SQL Server	16	March 26th, 2008 11:19 AM
Permissions on Tables, Stored Procedures, etc.	theChris	Microsoft SQL Server	2	March 24th, 2008 11:49 AM

DeveloperBarn

If statement stored procedure

If statement stored procedure

LinkBack to this Thread: http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html

Similar Threads