Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Administration (2000)  Fail to send email using CDO

Author  Topic  chand Starting Member 1 Post Posted - 2007-06-27 : 10:11:41 Hi, I am using SQL Server 2000 with windows 2003 Operating System. I Create one procedure which sends email and its working fine. Only thing is when I change "TO" email address to .gmail, .yahoo OR .hotmail and its stop sending this its only sending email with local email address like @parostechnologies.com (Which is my server) ***************** Code which I am using to send an email *************************** CREATE PROCEDURE [dbo].[SP_Send_CDOsysmail] @From varchar(100) , @To varchar(1000) , @Subject varchar(100)=" ", @Body text =" ", @IsHTML int, @BCc varchar(1000) = NULL, @AttachmentsPath varchar(1000) = NULL, @Attachments varchar(1000) = NULL /********************************************************************* To run this procedure if you are using different user other than sa than you have to give that user system administrator permission than you can call this procedure successfully. To do that in SQL Server Go to Security-->Server Roles and select system administrator and add your user here This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp ***********************************************************************/ AS BEGIN Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) --************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate') RETURN END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.parostechnologies.com' IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 1 IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END --Set BCc property IF (@BCc IS NOT NULL) BEGIN EXEC @hr = sp_OASetProperty @iMsg, 'BCc', @BCc IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT 'BCc sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END END EXEC @hr = sp_OASetProperty @iMsg, 'From', @From IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END IF @IsHTML = 0 BEGIN -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END END ELSE BEGIN -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body IF @hr <>0 BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END END --adding an attachment ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- IF (@AttachmentsPath IS NOT NULL) BEGIN If (@Attachments Is Not null) Begin Declare @charPos int Declare @Attachment varchar(1000) Set @CharPos = 1 While (@CharPos > 0) Begin Set @charPos = CHARINDEX(';',@Attachments) If(@charPos = 0) Begin Set @Attachment = @AttachmentsPath + SUBSTRING(@Attachments,1,len(@Attachments)) Exec @hr = sp_OAMethod @iMsg, 'Addattachment', NULL, @Attachment BREAK End Set @Attachment = @AttachmentsPath + SUBSTRING(@Attachments,1,@Charpos - 1) Set @Attachments = SUBSTRING(@Attachments,@Charpos + 1,len(@Attachments)) Exec @hr = sp_OAMethod @iMsg, 'Addattachment', NULL, @Attachment End End END ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL IF @hr <>0 OR @iMsg = NULL BEGIN SELECT @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send') 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 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send') GOTO send_cdosysmail_cleanup END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' GOTO send_cdosysmail_cleanup END END -- Do some error handling after each step if you have to. -- Clean up the objects created. send_cdosysmail_cleanup: If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it BEGIN EXEC @hr=sp_OADestroy @iMsg -- handle the failure of the destroy if needed IF @hr <>0 BEGIN select @hr INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy') EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT -- if sp_OAGetErrorInfo was successful, print errors IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy') END -- else sp_OAGetErrorInfo failed ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END END ELSE BEGIN PRINT ' sp_OADestroy skipped because @iMsg is NULL.' INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped') RETURN END END GO ************************************************************************************ When I am sending local email with domain parostechnologies.com its working fine but when I use other domain like gmail, yahoo OR hotmail then it gives me below error -2146824539 Description: The server rejected one or more recipient addresses. The server response was: 503 This mail server requires authentication when attempting to send to a non-local e-mail address. Please check your mail client settings or contact your administrator to verify th.... ------------------------------------------------------------------------------------------------------------------- ALSO IT GIVE ME THIS ERROR The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available ------------------------------------------------------------------------------------------------------------------- Can anyone help me what I have to change in my code.