Professional Web Applications Themes

Errors That stop execution of SP. - Microsoft SQL / MS SQL Server

1. Nope. You should know in advance however if a table (or column) exists or not. You can always find out with something like IF EXISTS(SELECT * FROM information_schema.tables where table_name = <table_name>, but basically your database schema should be stable and tables shouldn't just disappear. A linked server being unreachable is a runtime error, and you can't prepare for that. 2. Yes, any error returned by something outside SQL Server will terminate the batch, i.e. stop the stored procedure execution. This includes BULK INSERT, extended procedures, calling COM objects with the sp_OA% stored procedures, etc. Also if you try ...

  1. #1

    Default Re: Errors That stop execution of SP.

    1. Nope. You should know in advance however if a table (or column) exists or
    not. You can always find out with something like IF EXISTS(SELECT * FROM
    information_schema.tables where table_name = <table_name>, but basically
    your database schema should be stable and tables shouldn't just disappear. A
    linked server being unreachable is a runtime error, and you can't prepare
    for that.

    2. Yes, any error returned by something outside SQL Server will terminate
    the batch, i.e. stop the stored procedure execution. This includes BULK
    INSERT, extended procedures, calling COM objects with the sp_OA% stored
    procedures, etc. Also if you try to access columns that do not exists this
    will terminate the batch as well.

    3. Triggers and UDF's are worse. You can't trap _any_ errors inside them.
    Errors in UDF's and trigger will terminate the batch and rollback any open
    transactions. As UDF's and triggers are supposed to work like normal
    database functions, tables and constraints that makes a lot of sense,
    because SQL Server does not know with the current error handling if an error
    is actually handled, and having a possibly unhandled error in what should be
    an atomic, consistent transaction is a no-go.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Amy" <com> wrote in message
    news:phx.gbl... 
    execution 


    Jacco Guest

  2. #2

    Default Re: Errors That stop execution of SP.

    Amy (com) writes: 

    Yes. Load your procedures with AbaPerls, then you will be alerted when
    you load the procedures, that the tables are missing. AbaPerls is a toolset
    that we use in our shop, and that I have made available as freeware. You
    find it on http://www.abaris.se/abaperls(
     

    All compilations errors, such missing column, missing alias etc that were
    not detected when you loaded the procedure, causes the procedure to abort
    and execution continue in the caller.

    There is also a set of errors that causes the entire batch to be
    aborted, and any out-standing transaction to be rolled back.
     

    Yes. Except that any error in a trigger causes the batch to be aborted
    immediately.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. maximum execution time exceeded errors
    By Jeremy Watts in forum PHP Development
    Replies: 1
    Last Post: June 14th, 01:59 PM
  2. WebService TimeOut does not stop the thread execution
    By Jack Wright in forum ASP.NET Web Services
    Replies: 3
    Last Post: February 19th, 11:10 AM
  3. how to stop mail() errors going to postmasteretc...?
    By Mike in forum PHP Development
    Replies: 5
    Last Post: October 15th, 12:12 PM
  4. Replies: 0
    Last Post: July 20th, 03:35 PM
  5. Replies: 0
    Last Post: July 13th, 05:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139