sp_send_dbmail (The specified string is not in the form required for an e-mail address)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Corey Bunch » sp_send_dbmail (The specified string is not...

sp_send_dbmail (The specified string is not in the form required for an e-mail address)

Rate Topic

Display Mode

Topic Options

Author

Message

mail2_rony

Posted Wednesday, March 04, 2009 5:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 06, 2009 4:42 AM
Points: 2, Visits: 4

Hi!!

When I'm trying to send mail alerts through a procedure, it is giving the error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2009-03-03T19:33:53). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).

But the recipient mail id is correct, and i can send them mail though outlook. Please help me to identify the probable reasons for this kind of error. Any configuration issue?? test mails from the profile used is working fine. HOw to solve??

Post #668025

Jack Corbett

Posted Wednesday, March 04, 2009 7:53 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Can you post the code you are using?

Have you removed the mailing section of your procedure and tried it directly in SSMS?

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #668249

mail2_rony

Posted Thursday, March 05, 2009 4:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 06, 2009 4:42 AM
Points: 2, Visits: 4

declare @rgn varchar(3),
@usr_cd varchar (5000),
@usr_nm varchar (5000),
@eml_id varchar (5000),
@vc_mesgheading varchar(8000),
@vc_message varchar(8000),
@cntr_nm varchar(5000),
@rcpnt_cc varchar(8000)

select @rgn=rgn_cd from ccl_mst_cntr where cntr_cd=@srcicd and src_icd='zzzzz'

set @rcpnt_cc = ''

select @rcpnt_cc = @rcpnt_cc + ltrim(rtrim(a.email_id)) + '; '
from mst_user a,mst_user_role_link b where a.src_icd=b.src_icd and a.usr_empl_cd=b.usr_empl_cd
and b.role_cd='ROCSO'and a.actv='Y' and rgn_cd=@rgn
and isnull(email_id,' ')<>' ' and isnull(email_id,'.')<>'.'

--while exists(select 1 from #tempmail)
--begin

select top 1 @eml_id =email_id, @usr_nm=usr_empl_nm,@usr_cd=a.usr_empl_cd from mst_user a,mst_user_role_link b where a.src_icd=b.src_icd and a.usr_empl_cd=b.usr_empl_cd
and b.role_cd='SAPMM'and a.actv='Y' and rgn_cd=@rgn
and isnull(email_id,' ')<>' ' and isnull(email_id,'.')<>'.'

select @cntr_nm=cntr_nm from ccl_mst_cntr where cntr_cd=@srcicd

SELECT @vc_mesgheading = 'Dear '+ isnull( @usr_nm,'Friend')+',' + CHAR(13) + CHAR(13)

SELECT @vc_message = @vc_mesgheading + CHAR(13) +@cntr_nm
+ '(Storage location - "'+ isnull(@strloc,' ') +'") has received material against ' + CHAR(13)+
+ 'SAP STO number : '+ isnull(@sto,' ') + ' and Delivery Challan No. '+ isnull(@DCcd,' ') +''+ CHAR(13)+
+ 'dated '+ convert(varchar,@DCdt,103) + ' which have been entered in ENCORE stock ledger through'+ CHAR(13)+
+ 'MRR No. ' + isnull(@MMR,' ') +'.'+' dated '+ convert(varchar,@MMRdt,103) +' .'+ CHAR(13)+CHAR(13)
+ 'Please enter Goods Receipt in SAP.'+ CHAR(13)+ CHAR(13)+
'Regards, ' + CHAR(13) +
'Admin '

if((isnull(@eml_id,' ')<>' ') and (isnull(@rcpnt_cc,' ')<>' '))
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Vistadb1',
@recipients = @eml_id,
@copy_recipients = @rcpnt_cc,
@blind_copy_recipients = 'ronyroyr@xxx.com;mail2_rony@xxx.com;',
@body = @vc_message,
@subject = 'Received Details' ;
end

Post #669063

rudy - Doctor "X"

Posted Thursday, March 05, 2009 8:52 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:11 PM
Points: 3,108, Visits: 2,114

Why don't you try a low-tech debugging aid

print @eml_id
print @rcpnt_cc

Just prior to the exec for the mail send and see of the addresses are OK. You could even cut them out and paste then into your mail client to see what happens when you try and send them.

If that provides no new leads then talk to your email admins and have them jump in with additional diagnostics message that are available to them that are provided by your SMTP server as well !

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."

Post #669914

« Prev Topic | Next Topic »

Permissions