April 1, 2008 at 8:19 am
Hi,
:w00t: Error while sending mail using stored procedure to recipients.
it is giving some error no like: -2147220973
it will be glad help if you solve this issue for me. 😀 thnx in advance (Who ever)
CREATE procedure usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10),
@cc varchar(100)=null,
@bcc varchar(100)=null
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject
exec @hr = sp_oasetproperty @imsg, 'cc', @cc
exec @hr = sp_oasetproperty @imsg, 'bcc', @bcc
-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null
-- sample error handling.
if @hr <>0
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg
April 1, 2008 at 9:01 am
I can't find that error in a search, but you might try running this from the command line (VBScript) and see if there's a better error returned.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 1, 2008 at 9:20 am
Since you have @smtpserver defined as a VARCHAR(25) I would look at the length of what's being passed in for that.
Edit: The reason for that error may be that the SMTP servername is being truncated and CDO subsequently is sending to an invalid server. From looking at the other declarations you might have intended it to be a VARCHAR(255).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply