Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Posts
    9

    Unanswered: xp_sendmail query

    I have 3 jobs that use xp_sendmail to run queries and send users some quick reports. These run at staggered times about 1/2 hour apart from one another.

    They work fine most of the time, however, once every 2 or 3 weeks, they will all lock up and continue to run without ever finishing. Each one usually takes about 3 seconds to run but once they lock up they will run until I cancel the job. They are simple little SELECT statements. When I check for locking within the database, I can find no locks. This makes me think this is not a table locking problem, so I assume that there is something wrong with the way it is connecting to MS Exchange.


    Any suggestions? Has anyone seen anything like this?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    6

    Re: xp_sendmail query

    Are you using a DTS Package or just a TSQL command?

  3. #3
    Join Date
    Oct 2002
    Posts
    9
    No DTS, just pure T-SQL.
    It is just a simple query in the query parameter of xp_sendmail. Where the call to xp_sendmail is the only step in the job that runs every morning.

    It basically looks like the below:
    EXECUTE master.dbo.xp_sendmail
    @recipients = 'me@hotmail.com',
    @subject = 'Morning Report',
    @message = 'Look at this data:',
    @query = 'EXECUTE someselect_storedprocedure',
    @attach_results = 'true',
    @no_output = 'true',
    @width = 300

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    RE:
    Originally posted by britton
    I have 3 jobs that use xp_sendmail to run queries and send users some quick reports. These run at staggered times about 1/2 hour apart from one another. They work fine most of the time, however, once every 2 or 3 weeks, they will all lock up and continue to run without ever finishing. ...
    ...It is just a simple query in the query parameter of xp_sendmail. Where the call to xp_sendmail is the only step in the job that runs every morning. It basically looks like the below:
    EXECUTE master.dbo.xp_sendmail
    @recipients = 'me@hotmail.com',
    @subject = 'Morning Report',
    @message = 'Look at this data:',
    @query = 'EXECUTE someselect_storedprocedure',
    @attach_results = 'true',
    @no_output = 'true',
    @width = 300
    Q1 Has anyone seen anything like this?
    A1 Yes. Your tasks may be colliding with reindexing or something that runs once every 2 or 3 weeks on the Sql Server, or there may be similarly occuring resource related issues on the Exchange Server.

    Q2 Any suggestions?
    A2 Yes. Gather more data to more clearly identify where (and what) the issue(s) may be. For example you might try something like:
    i Create three additional sendmail jobs to mainly test mailserver linkage (schedule each say one minute before each report goes out). Set them to be sent to yourself with some query that does not interact with your DB objects e.g., set @query = 'Select ''TestOfSendMailOnlyResultSet'' As TestOfSendMailOnly'(add job output files so you may view them when the next failure occurs).
    ii Create three additional tsql jobs (schedule each say one minute before each report goes out) to execute each of the three stored procedures (add output files so you may view their results when the next failure occurs).

  5. #5
    Join Date
    Oct 2002
    Posts
    9

    Finally a failure message

    I have pinpointed the job that is causing the problem. As I said before, it is just a simple select statement in the xp_sendmail query parameter. However, now I think the problem resides in SQL Server or SQL Agent. I checked the Job History on the failing job and found the following message. Does this seem familiar to anyone? Would a collision with reindexing cause this? Thanks for your help already.

    "SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. [SQLSTATE HY000] (Error 0). The step failed."

  6. #6
    Join Date
    Oct 2002
    Posts
    369
    Q1 I have pinpointed the job that is causing the problem. As I said before, it is just a simple select statement in the xp_sendmail query parameter. However, now I think the problem resides in SQL Server or SQL Agent. I checked the Job History on the failing job and found the following message. Does this seem familiar to anyone?
    A1 Yes, possibly. Looking at actual job output from the sp and or tsql likely would help.

    Q2 Would a collision with reindexing cause this?
    A2 Maybe, (does your reindexing job put the db into single user mode, or does its timing coincide with when your reindexing jobs are say, recreating indexes your stored procedure would likely use?). However since you are just getting an AV as a clue right now; I suggest gathering more data to find out why should more clearly identify where (and what) the issue(s) may be. For example you might try something like:
    i Create an additional tsql job; with two job steps steps to execute
    A the offending stored procedure and
    B just its tsql
    (add output files for each step so you may view their results when the next failure occurs).

  7. #7
    Join Date
    Oct 2002
    Posts
    9

    The solution

    Just thought I would wrap this up. It turns out the problem I was having was a known problem with SQL Server 2000 sp2. Read all about it.

    Q320407 FIX: SQLMail Extended Stored Procedures May Fail with an Access
    http://support.microsoft.com/default...;EN-US;Q320407



    Q329375 FIX: Access Violations May Occur with SQL Mail When You Use XP_SENDMAIL
    http://support.microsoft.com/default...;EN-US;Q329375

  8. #8
    Join Date
    Jul 2002
    Posts
    63
    You can create second job that all his purpose is to operate SP_Kill to the last Send mail job .The idea is that if the job is still running than kill it !

  9. #9
    Join Date
    Oct 2002
    Posts
    9

    xp_sendmail patch

    Good idea, but MS provided a patch since this was their code was what was causing the problem. After we applied the patch, I haven't seen the problem again.

Posting Permissions

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