July 19, 2010 at 6:39 am
Hi,
the following query we are executing one of our prod server
Use abc
update mailstable set mailsent='N' where id=123
exec sp_sendmailsimmediately 123
results
NULL
we are getting following error Messages
123
(1 row(s) affected)
Error Sending e-mail.
CDO.Message.1
The pickup directory path is required and was not specified.
(1 row(s) affected)
Please help to fix this issue we are using sql 2005
July 19, 2010 at 6:46 am
James i'm guessing that you'd need to post the code for the procedure sp_sendmailsimmediately
clearly it's using CDO instead of the newer msdb.dbo.sp_send_dbmail, and a required parameter is missing.
Lowell
July 19, 2010 at 10:45 pm
Thanks for the reply Please check the code
USE [MailSending]
GO
/****** Object: StoredProcedure [dbo].[sp_SendMailsImmediately] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec sp_sendmails
--select * from repository.dbo.mailstable
--update repository.dbo.mailstable set mailsent='N'
CREATE procedure [dbo].[sp_SendMailsImmediately] (@tableID int)
as
declare @id integer
declare @receipt varchar(50)
declare @content varchar(8000)
declare @sub varchar(250)
DECLARE @LogID int
declare @module varchar(200)
declare @errorDesc varchar(1000)
declare @attachment varchar(8000)
declare @mailSendFrom varchar(1000)
print 'inside send mails immediately'
print @tableID
declare mailcur cursor for
select id,RECIEPIENT,convert(varchar(8000),content),subject, module,convert(varchar(8000),attachmentName)
from mailstable where MailSent='N' and ID=@tableID;
open mailcur
fetch mailcur into @id, @receipt, @content,@sub, @module,@attachment
while @@fetch_status=0
begin
/* if @receipt = 'app@abc.com'
Begin
set @receipt='xyz@abc.com;bba@abc.com'
End */
if @module='app'
begin
set @mailSendFrom='app@abc.com'
end
else
begin
set @mailSendFrom='app@abc.com'
end
declare @error int
/*
exec @error= master.dbo.xp_smtp_sendmail
--@FROM ='app@abc.com',
@FROM =@mailSendFrom,
@TO =@receipt,
@server='10.10.X.X',
--@server='servername.domainname',
@subject=@sub,
@message=@content,
@type = N'text/plain',
@attachments= @attachment
*/
select @attachment
exec sp_SQLSMTPMail 'james@abc.com',
@content,
@sub,
@attachment,
null,
@mailSendFrom,
null,
null,
1,
'smtp.gmail.com'
,'1','587','0','0','30',NULL,'dbname'
IF(@@ERROR<>0 or @error<>0)
BEGIN
SELECT @LogID=ISNULL(MAX(ID),0)+1 FROM MailsLog
select @errorDesc = description from master.dbo.sysmessages where error=@error
INSERT INTO MailsLog(ID,Module, MailsTableID,Reciepient,SendingStatus,ErrorMessage,CreatedOn,ModifiedOn)
VALUES(@LogID,@module, @id,@receipt,'Failed',@errorDesc,getdate(),getdate())
END
else
begin
Update mailstable set MailSent='Y', ModifiedOn=getdate() where id=@id;
end
fetch mailcur into @id, @receipt, @content,@sub, @module , @attachment
end
close mailcur
deallocate mailcur
Is there any error in this query Please suggest
July 20, 2010 at 11:11 pm
suggestions please
July 22, 2010 at 3:47 am
I'm not familiar with the sp_SQLSMTPMail stored procedure - is there a reason you need to use this rather than Database Mail (since you have SQL 2005)?
July 23, 2010 at 12:19 pm
I agree.... This has an old school feel to it! Yeah use the new 2005 proc's
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply