January 3, 2013 at 6:04 pm
I have a table that has less then 200 emails in and a stroded proc to send emails I done get any email. but if I use it in the test db with only about 10 emails I get the email. Dont know if its because i have my database email setup to use gmail to send the email.
here is my code.
USE [Soccer]
GO
/****** Object: StoredProcedure [dbo].[usp_Send_Soccer_File] Script Date: 01/03/2013 19:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_Send_Soccer_File]
as
DECLARE ----@TodaysDate date = getdate(),
@PhoneNumber nvarchar(max),
@FirstName varchar(25),
@body nvarchar(MAX),--= ' Soccer '
@filename nvarchar(MAX),
@EmailAddress varchar(50)
DECLARE @Soccer TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(max), FirstName varchar(25), EmailAddress varchar(50))
--set Identity_insert [@Soccer] on
INSERT @Soccer
SELECT PhoneNumber, FirstName, EmailAddress from dbo.Names
DECLARE @NumberOfSoccer smallint = (SELECT COUNT(*) from @Soccer)
DECLARE @MinID int
WHILE @NumberOfSoccer > 0
BEGIN
SET @MinID = (SELECT MIN(ID) From @Soccer)
--SET @PhoneNumber = (SELECT PhoneNumber from @Soccer where ID = @MinID)
set @FirstName = (Select FirstName from @Soccer where ID = @MinID)
Set @EmailAddress = (Select EmailAddress from @Soccer where EmailAddress is not NULL and ID = @MinID)
SET @body = @FirstName + ' ' + 'Soccer Signups are January 12, 2013 10:00am to 2:00pm, January 19, 2013 10:00am to 2:00pm, January 26, 2013 10:00am to 2:00pm, and February 2, 2013 10:00am to 2:00pm all signups are at the Chamber of Commerce(1)'
set @filename = 'C:\soccer_flyer.pdf';
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Soccer',
@recipients = @EmailAddress,
@file_attachments = @filename,
--@blind_copy_recipients = @Email, ----***@*****.com',
@body = @body ,
@profile_name ='Soccer';
DELETE FROM @Soccer where ID = @MINID
SET @NumberOfSoccer = @NumberOfSoccer -1
END
--END
RETURN;
January 3, 2013 at 8:18 pm
When you execute for 200 emails, do you see "Mail queued." appear in the Messages pane for each call to EXECUTE msdb.dbo.sp_send_dbmail?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 3, 2013 at 8:37 pm
yes but when I go into the sql server log it get the mail could not be sent to the recipients because of the mail server failure. etc
January 3, 2013 at 8:48 pm
Most likely then either you haven't configured the mail server correctly or for some reason it is not up at that time. GMAIL should work (I use it here). Another possibility is that maybe the server sending email can't see the mail server.
Can you ping the mail server's IP from the sending server?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 3, 2013 at 9:04 pm
maybe I figured it out I was using port 25 instead of 587.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply