Announcement

Collapse
No announcement yet.

MSSQL send mail

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MSSQL send mail

    Hello folks!

    I have a problem migrating from SQL 2000 to SQL 2005 express...

    The problem is, that Stored procedure which worked superb in SQL 2000 now won`t send mail (SQL 2005 express).

    I have to modify this script, but unfortunatly I d`t know how to make it SQL 2005 compatible...

    So heres the script:

    Code:
    [dbo].[sp_send_cdosysmail1] 
    	@From varchar(100) ,
    	@To varchar(100) ,
    	@Bcc varchar(100),
    	@Subject varchar(100)=" ",
    	@Body varchar(8000) =" ",
    	@AttachmentName varchar(255) = " ",
    	@Result int OUTPUT
    
    /*********************************************************************
    
    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. ***********************************************************************/ 
    AS
    	Declare @iMsg int
    	Declare @hr int
    	Declare @i 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', 'MY-SMTP-SERVERNAME' 
    
    	EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    
    	EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
    	EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @Bcc
    	EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
    	EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
    
    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    	EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
    
    	IF (@AttachmentName<>' ')
    		EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @i OUTPUT, @AttachmentName
    	EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
    
    	SET @[email protected]
    	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
    
    -- Do some error handling after each step if you have to.
    -- Clean up the objects created.
    EXEC @hr = sp_OADestroy @iMsg
    This code also sends email attachments trough SMTP server...
    We have exchange 2007 I already configured it to recive mail from this server where SQL 2005 is installed on...

    Can someone advise or help me with this?


    Thanks in advance!

  • #2
    Re: MSSQL send mail

    Already solved it...

    It wasn`t the script, it was exchange 2007 server internal relaying...

    Comment

    Working...
    X