Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail (The specified string is not in the form required for an e-mail address) Expand / Collapse
Author
Message
Posted Wednesday, March 4, 2009 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 6, 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
Posted Wednesday, March 4, 2009 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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
Posted Thursday, March 5, 2009 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 6, 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
Posted Thursday, March 5, 2009 8:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 3,193, Visits: 2,290
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 »

Add to briefcase

Permissions Expand / Collapse