Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: XPSMTP - Almost there

    Hi guys,

    After searching your site (great site btw) i cannot find a solution to the issue i am having with XPSMTP. And it seems you guys have some fair amount of experience when dealing with it

    Ive followed the instructions to the tee from the site - http://www.sqldev.net/xp/xpsmtp.htm

    I have extracted the dll to the BINN folder and registered according to the instructions (is there a way to dbl check that the DLL is registered ? I do notice an entry in the event viewer to the tune of: "Using 'xpsmtp80.dll' version '2002.08.12' to execute extended stored procedure 'xp_smtp_sendmail'.")

    I did the assign permission thing:

    Grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:
    grant execute on xp_smtp_sendmail to public
    By default only the member of the sysadmin role have execution rights on the XP after it is being registered

    ive created a SP with the following props:

    note: ive replaced my actual domain with domain.co.uk in these code copies

    Code:
    CREATE Procedure xp_smtp_sendmail
    
    	@SenderName varchar(100),
    	@SenderAddress varchar(100),
    	@RecipientName varchar(100),
    	@RecipientAddress varchar(100),
    	@Subject varchar(200),
    	@Body varchar(8000),
    	@MailServer varchar(100) = 'mail.domain.co.uk'
    
    	AS	
    	
    	SET nocount on
    GO
    I then call that SP using the following in Query Analyzer:

    Code:
    exec xp_smtp_sendmail @SenderName='#SQLService', @SenderAddress='#sqlservice@domain.co.uk', 
    @RecipientName = 'Helpdesk', @RecipientAddress = 'helpdesk@domain.co.uk', 
    @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
    Which executes successfully.

    The trouble im having is that the email never arrives!

    theres no error or anything, it just never arrives!

    Ive also checked the connection to the mail server with the following parameters:

    Code:
    -- ping server using @server and @port
    declare @rc int
    exec @rc = master.dbo.xp_smtp_sendmail @server = N'mail.domain.co.uk', @port = 25, @ping = 1
    select @rc
    and it comes back with 0 which = no problems!

    Any wise words?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This might be a silly question - but is the mail server configured/running?
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Just a thought, but I successfully use that, and I use the internal IP address of the server rather than "N'mail.domain.co.uk'". I can't remember now if I tried it that way and failed, but for me it definitely works with the IP address (an Exchange server, if it matters).
    Paul

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Oh, and I don't think you followed the directions to a tee. You've named your SP the same as theirs, which may cause confusion, and you don't execute it the way they demonstrate. Here's a working SP:

    Code:
    CREATE PROCEDURE [dbo].[procSendEmail] 
    
    @TO as NVARCHAR(4000),  @message as NVARCHAR(4000), @Subject as nvarchar(100)
    
    AS
    
    declare @rc INT
    
        exec @rc = master.dbo.xp_smtp_sendmail
            @FROM = 'airport@domain.com',
            @FROM_NAME = N'Name here',
            @TO = @TO,
            @BCC = 'paulb@domain.com',
            @subject = @Subject,
            @message = @message,
            @server = N'10.1.1.6',
            @type = N'text/plain'
    
    select RC = @rc
    GO
    Paul

  5. #5
    Join Date
    Jul 2007
    Posts
    2
    Quote Originally Posted by georgev
    This might be a silly question - but is the mail server configured/running?
    You're right, that was a sily question! But thank you for responding!


    Quote Originally Posted by pbaldy
    Just a thought, but I successfully use that, and I use the internal IP address of the server rather than "N'mail.domain.co.uk'". I can't remember now if I tried it that way and failed, but for me it definitely works with the IP address (an Exchange server, if it matters).
    Ok, i replaced the name with the IP still never worked.


    Quote Originally Posted by pbaldy
    Oh, and I don't think you followed the directions to a tee. You've named your SP the same as theirs, which may cause confusion, and you don't execute it the way they demonstrate. Here's a working SP:

    Code:
    CREATE PROCEDURE [dbo].[procSendEmail] 
    
    @TO as NVARCHAR(4000),  @message as NVARCHAR(4000), @Subject as nvarchar(100)
    
    AS
    
    declare @rc INT
    
        exec @rc = master.dbo.xp_smtp_sendmail
            @FROM = 'airport@domain.com',
            @FROM_NAME = N'Name here',
            @TO = @TO,
            @BCC = 'paulb@domain.com',
            @subject = @Subject,
            @message = @message,
            @server = N'10.1.1.6',
            @type = N'text/plain'
    
    select RC = @rc
    GO
    Ive used this SP that u gave me and it works a treat, mail is coming through fine! So thanks for that!

    Does it really matter what i called it anyway? I simply kept their name so that i wouldn't get mixed up!

    Im assuming i can use the same code to send attachments as well? i just need to specify an attachment argument?

    thank you for you help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •