Send email from Sql Job Schedule

hi,
have table called customer name , birthday , email address stored.
trying send email person birth day in current day , month creating job schedule in sql server 2005. job scheduled daily.

job schedule command below

declare   @out_desc varchar(1000),  @out_mesg varchar(10)    declare @name varchar(20),  @birthdate datetime,  @email nvarchar(50)    declare @body nvarchar(1000)    declare c1 cursor read_only   select [name], [birthdate], [email]  customerdetails_data.dbo.customers    open c1  fetch next c1   @name, @birthdate, @email  while @@fetch_status = 0  begin                  set @body = '<b>happy birthday ' + @name + '</b><br />many happy returns of day'                  + '<br /><br />customer relationship department'                  exec customerdetails_data.dbo.sp_send_mail                   'testss@company.net', --- add email address here                  '***********',       ----add password here                  @email,                  'birthday wishes',                   @body,                  'htmlbody',                   @output_mesg = @out_mesg output, @output_desc = @out_desc output                    print @out_mesg                  print @out_desc                    fetch next c1           @name, @birthdate, @email  end  close c1  deallocate c1

job schedule call stored procedure customerdetails_data.dbo.sp_send_mail. , below code stored procedures:

set ansi_nulls on  set quoted_identifier on  go  alter procedure [dbo].[sp_send_mail]                  @from varchar(500) ,                  @password varchar(500) ,                  @to varchar(500) ,                                @subject varchar(500),                  @body varchar(4000) ,                  @bodytype varchar(10),                  @output_mesg varchar(10) output,                  @output_desc varchar(1000) output   declare @imsg int  declare @hr int  declare @source varchar(255)  declare @description varchar(500)    exec @hr = sp_oacreate 'cdo.message', @imsg out    --sendusing specifies whether send using port (2) or using pickup directory (1)  exec @hr = sp_oasetproperty @imsg,  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'    --smtp server  exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',     'smtp.ntc.net.np'     --username  exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',     @from     --password  exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',     @password     --usessl  exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',     'true'     --port   exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',     --'465'   '30'  --requires aunthentication none(0) / basic(1)  exec @hr = sp_oasetproperty @imsg,     'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',     '1'   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  -- if using html e-mail, use 'htmlbody' instead of 'textbody'.  exec @hr = sp_oasetproperty @imsg, @bodytype, @body  exec @hr = sp_oamethod @imsg, 'send', null  set @output_mesg = 'success'  -- sample error handling.  if @hr <>0           select @hr          begin                  exec @hr = sp_oageterrorinfo null, @source out, @description out                  if @hr = 0                  begin                          --set @output_desc = ' source: ' + @source                          set @output_desc =  @description                  end          else          begin                  set @output_desc = ' sp_oageterrorinfo failed'          end          if not @output_desc null                          set @output_mesg = 'error'  end  exec @hr = sp_oadestroy @imsg

looking @ history log see following message:

executed user: nt authority\system. error [sqlstate 01000] (message 0) transport failed connect server. [sqlstate 01000] (message 0) error [sqlstate 01000] (message 0) transport failed connect server. [sqlstate 01000] (message 0). step succeeded.

, no email sent customer. can me.

thanks

hello.

you not able connect you´re mail server see it.

double check youre email settings , make sure server can connection required.

 

 

 

SQL Server  >  SQL Server Tools