Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Unanswered: exec xp_sendmail error on SQL Server 2000

    I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
    So I try to have it executed in a trigger but it failed.

    Here is the trigger creation script and error message

    use mlcb
    go
    if exists (select name
    from sysobjects
    where name = 'test' and
    type = 'TR')
    DROP TRIGGER TEST
    GO

    CREATE TRIGGER test on mlcb.dbo.trans_errlog
    for insert
    as
    declare @email_subject varchar(100),
    @email_content varchar(4000),
    @email_recipients varchar(50)

    set @email_subject='SQL Mail test mail'
    set @email_recipients='some@world.com.tw'
    set @email_content='this is a test mail, don't reply this mail'

    exec master.dbo.xp_sendmail @recipients=@email_recipients,@subject=@email_subj ect,@message=@email_content
    GO

    Error Message:
    Server: Msg 2812, Level 16, State 62, Line 6
    Could not find stored procedure 'master.xp_startmail'.
    The statement has been terminated.

    Appreciate any prompt reply.

    JD

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    before we get to your problem, let's talk about this for a second.

    is your logic valid if more than one record is inserted at a time?

    have you thought about the associated overhead for each transaction here?

    have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

    I am nearly certain your problem is permissions related. which is another can of worms.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2003
    Posts
    11
    Quote Originally Posted by Thrasymachus
    before we get to your problem, let's talk about this for a second.

    is your logic valid if more than one record is inserted at a time?

    have you thought about the associated overhead for each transaction here?

    have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

    I am nearly certain your problem is permissions related. which is another can of worms.
    Thanks very much for your reminders
    I really didn't think about these.

    Only one record is inserted at a time.
    There is no much transaction, just something like error notification.

    mmmmm, I didn't know unavailable exchange server will cause such issue.
    So how can I avoid it?

    How to fix the permission issue?

    Thanks for your help!!

    JD

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).

    Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.

    That said,

    Error Message:
    Server: Msg 2812, Level 16, State 62, Line 6
    Could not find stored procedure 'master.xp_startmail'.
    The statement has been terminated.
    Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.

    Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Aug 2003
    Posts
    11

    Red face

    hi hmscott,

    Thanks for helping me away such dangerous condition.

    It is really bad hear that. I thought I am almost there.
    I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.

    Can you provide any reference for SMTP usage?

    But I am still want to know how to solve the issue I have right now.
    I can run the script out of trigger.

    I did exec master.dbo.xp_startmail.

    master.xp_startmail was in the error message when firing the trigger.

    Best Regards,
    JD

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...

    As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.

    Also, did you check for the existence of the master.dbo.sp_startmail proc?

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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