Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: xp_sendmail problem

    Hi all,

    Here's a stored procedure I've been working on, managed to get this far thanks to derrickleggett on yesterdays post however, I find that when I use the xp_sendmail procedure after the error handle (see below) it doesn't work, but if I put it anywhere else in the proc it does - can't understand why??

    CREATE PROCEDURE sproc_48hrAccess_UploadTest

    AS

    BEGIN TRANSACTION Data_Upload

    DECLARE @error_msg VARCHAR(255)

    INSERT INTO tblTest
    SELECT
    tblTestIn_Field1
    FROM tblTestIn

    INSERT INTO tblTest
    SELECT
    tblTestIn2_Field1
    FROM tblTestIn2

    IF @@ERROR <> 0
    BEGIN
    SELECT @error_msg = 'sproc_48hrAccess_UploadTest::Failure on tblTestIn insert.'
    GOTO error_handle
    END

    COMMIT TRANSACTION Data_Upload
    RETURN 0

    error_handle:
    ROLLBACK TRANSACTION Data_Upload
    RAISERROR (@error_msg,16,1)
    EXEC master..xp_sendmail 'matt.mcdonald@aapct.scot.nhs.uk', 'hello test'
    RETURN 1

    GO

    Your thoughts on this would be appreciated...

  2. #2
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unhappy

    It seems to be that when the EXEC xp_sendmail statement is placed after an error occurs it doesn't work...?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is a large part of why I suggested using a job instead of a stored procedure to send the email. Jobs are safer that way!

    -PatP

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Hi Pat, thanks for replying, I know what your saying. However, it appears that it's the actual error trapping that's not working. Have a look at the very simple code below, maybe you could run it on your SQL server to test it for me:

    INSERT INTO tblTestr
    SELECT tblTestIn_Field1
    FROM tblTestIn

    IF @@ERROR <> 0
    BEGIN
    PRINT "TRANSACTION FAILED"
    END

    I've deliberately given the table a wrong name calling it 'tblTestr' instead of 'tblTest' so that it throws up error '208'. However the error statement does not catch the error it just ignores it. When I run it in Query Analyzer it shows the following results:

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'tblTestr'.

    Instead of printing "TRANSACTION FAILED"

    I don't understand as everywhere I look this seems to be the accepted method for trapping errors. Any help u could give would be much appreciated..

    Matt

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that I see the problem!

    Transact SQL is a data manipulation language. Its purpose is to manage data on a server, not to write full blown programs (although I often press T-SQL into service for things that would give the designers a heart attack).

    Errors fall into several classes. Some errors are syntactical in nature, meaning that the command won't work as given under any circumstances. Other errors are procedural (run time) in nature, meaning that the command itself coule work, but not in the present environment. The error trapping in SQL Server is meant to catch the procedural errors, the host language (VB, VC, C#, etc) needs to handle the syntactical errors.

    The kind of problem you are trying to catch (a bad object name) isn't meant to be processed via @@error handling. It is meant to be processed via the client programming language.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Gotcha, cheers. I now know what I have to do...

Posting Permissions

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