Professional Web Applications Themes

Problem with raiserror - Microsoft SQL / MS SQL Server

Hello, I've got a procedure which after encountering error 547 does a RAISERROR with a detailed error message. This works fine, if there is no procedure called between trapping the error 547 and the RAISERROR statement. But as soon as you call a procedure (in this case a procedure to create the error message) before the RAISERROR statement the error cannot be detected by ADO (only the errors which SQL SERVER itself produces). However when calling the procedure from the SQL Query yzer all the errors from the error stack (also the one from the RAISERROR statement) can be seen. ...

  1. #1

    Default Problem with raiserror

    Hello,
    I've got a procedure which after encountering error 547
    does a RAISERROR with a detailed error message.
    This works fine, if there is no procedure called between
    trapping the error 547 and the RAISERROR statement. But as
    soon as you call a procedure (in this case a procedure to
    create the error message) before the RAISERROR statement
    the error cannot be detected by ADO (only the errors which
    SQL SERVER itself produces).
    However when calling the procedure from the SQL Query
    yzer all the errors from the error stack (also the one
    from the RAISERROR statement) can be seen.
    Has got anyone a clue if there is a way to get around
    this problem.

    Thanks for any suggestions
    Monika

    ..


    Monika Guest

  2. #2

    Default Re: Problem with raiserror

    No, the procedure returns no resultset, just a vachchar
    string as an output parameter which will be the parameter
    for the RAISERROR statement. The application itself does
    already a loop through the error collection but the error
    of the RAISERROR-Statement does not appear.

     
    returning some 
    immediately. 
    all the errors. 
    need any. 
    Monika Guest

  3. #3

    Default Re: Problem with raiserror

    No, the procedure returns no resultset, just a vachchar
    string as an output parameter which will be the parameter
    for the RAISERROR statement. The application itself does
    already a loop through the error collection but the error
    of the RAISERROR-Statement does not appear only if the
    raiserror statement is called
    without calling the procedure before.
    Can't find anything concerning this problem in the archive.
    Do you have anymore suggestions?
     
    returning some 
    immediately. 
    all the errors. 
    need any. 
    Monika Guest

  4. #4

    Default Re: Problem with raiserror

    Just a thought. Make sure you have SET NOCOUNT ON at the beginning of your
    stored procedures.

    If this doesn't help, please post your stored procedure scripts, so we can
    have a look.

    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm




    "Monika Frielingsdorf" <de> wrote in message
    news:09db01c355b6$d3016a40$gbl...
    No, the procedure returns no resultset, just a vachchar
    string as an output parameter which will be the parameter
    for the RAISERROR statement. The application itself does
    already a loop through the error collection but the error
    of the RAISERROR-Statement does not appear only if the
    raiserror statement is called
    without calling the procedure before.
    Can't find anything concerning this problem in the archive.
    Do you have anymore suggestions?
     
    returning some 
    immediately. 
    all the errors. 
    need any. 


    Narayana Guest

  5. #5

    Default Re: Problem with raiserror

    You just need to capture the resultset into a ADO Recordset object and call
    the NextRecordset method of this recordset. Someting like Set Rs =
    Rs.NextRecordset

    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm


    "Monika Frielingsdorf" <de> wrote in message
    news:0c6501c3573f$93cb5b80$gbl...
    The procedure does a dynamic select and therefore produces
    one row but somewhere in the middle of the code.
    How can ADO access this resultset in order to close it?
    It seems like, because the procedure not only consists of
    one select-statement, ADO cannot access the resultset.
    We have set the NOCOUNT- Parameter to on. If the dynamic
    select within the procedure is not executed then the
    error_msg can be raised.

     
    using the 
    >returning some 
    >immediately. 
    >all the errors. 
    >need any. [/ref]
    as [/ref]
    which [/ref]
    one 
    >
    >
    >.
    >[/ref]


    Narayana Guest

Similar Threads

  1. ADOConnection loses RAISERROR from SQL Server
    By James in forum ASP Database
    Replies: 1
    Last Post: November 14th, 02:33 PM
  2. Replies: 0
    Last Post: September 19th, 06:55 AM
  3. Replies: 0
    Last Post: September 19th, 04:59 AM
  4. RAISERROR
    By Johannes Lebek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 12:09 PM

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