February 12, 2003 at 8:39 am
Strange one this. I have a DTS package that calls the following stored procedure:
---------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_send_pending
@freq char(1) = 'i'
AS
set xact_abort on
declare @a_curs cursor
declare @recipient varchar(50), @message varchar(7800), @subject varchar(100), @id int
declare @r int
/*
exec @r = master..xp_startmail
if @r !=0
begin
return
end
*/
set @a_curs = cursor
for
select recipient
, message + disclaimer as message
, subject
, sendid
from tosend
where whentosend = @freq
and suspended = 0
open @a_curs
fetch next from @a_curs
into
@recipient
, @message
, @subject
, @id
while @@fetch_status = 0
begin
exec @r = master..xp_sendmail @recipients=@recipient, @message=@message, @subject=@subject
if @r = 0
begin
-- move from pending to sent table
insert into sent
(
sendid
, appid
, userid
, recipient
, message
, subject
, userwhoadded
, disclaimer
)
select sendid
, appid
, userid
, recipient
, message
, subject
, userwhoadded
, disclaimer
from tosend
where sendid = @id
delete
from tosend
where sendid = @id
end
else
begin
print @r
print @recipient
end
fetch next from @a_curs
into
@recipient
, @message
, @subject
, @id
end
close @a_curs
GO
----------------------------------------------------------------------------
The DTS package is failing as soon as the xp_sendmail line within the stored procedure attempts to send an email to an invalid email account e.g joe.bloggs.invalid.com
This never use to happen. The DTS package use to continue processing all valid emails even if the stored procedure cursor encountered an invalid email address.
The DTS package is scheduled by a SQL job to run once a day.
Any ideas as to why this is no longer working? We are running SQL 7.0 sp 4.
Is this a problem with our MAPI client or DTS or the stored proc? I'm baffled!
February 12, 2003 at 9:02 am
If you can run the dts manually from EM and it works fine and from an schedule Job you can't, then It must be a problem with the SQL server Agent loggin account.
Remember that it must be a domain account to use SQL mail with the Agent.
February 12, 2003 at 9:36 am
The scheduled job runs OK. The problem occurs when executing the DTS both by manual and automated methods. Everything is fine until the cursor within the stored procedure (see previous code listing) being called by the DTS package encounters an invalid email account.
Myles
quote:
If you can run the dts manually from EM and it works fine and from an schedule Job you can't, then It must be a problem with the SQL server Agent loggin account.Remember that it must be a domain account to use SQL mail with the Agent.
July 18, 2003 at 10:12 am
I am having the same problem and was curious as to if there is a resolution.
July 21, 2003 at 7:23 am
Check your @recipient variable when the package fails. Probably the email address is longer than 50 characters allow in the variable. In which case a truncated (and therefore malformed) email address is sent to xp_sendmail. This was the situation when I encountered the same problem a while ago.
Joseph.
Joseph
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply