Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Red face Unanswered: HELP! How to get the error text within a Sybase stored proc?

    I want to read the error text (or message) from within a Sybase stored procedure.

    I have the value of @@error and I know you can find an entry for it in master..sysmessages, but what I want is the concrete error message.

    I mean, instead of the generic template for the message, such as:

    "SELECT INTO failed because column %d in table '%.*s' has a null column name. Null column names are not allowed."

    I would like to have the message associated to the actual error, which should rather look like this:

    "SELECT INTO failed because column 3 in table 'dbo.SOMETHING' has a null column name. Null column names are not allowed."

    I've been googling for hours and have found no solution so far. Is there any stored procedure I could call to get the message? Any log table? Any idea?

    Your help will be highly appreciated. Thanks a LOT in advance!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Why not just go into the stored proc and supply a field name for each field in your select into statement? I can't see how the error message can be any clearer. If you fix the bug then you won't need to improve the message any further.

  3. #3
    Join Date
    Sep 2010
    Posts
    4
    Not a bad idea, but it would be quite painful doing this, statement after statement.

    After every statement which could possibly fail (and which are quite complex in my case), I already check for @@error, store it in a local variable if something went wrong, and then go to an error handling section.

    I just want to get the message associated to the error (which is by the way displayed in my IDE) logged into a table. Is this possible?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Then rather than inserting into a temp table just select out the data. Look at the output and, if you see a blank field name, fix the bug.

  5. #5
    Join Date
    Sep 2010
    Posts
    4
    Thanks again, but this will imply I'll have to parse every single statement in my sproc; and well.... I've been saying "my sproc", but I didn't write it. I'm just maintaining it.

    So, all what I want to do is logging into a table all the errors that particular stored procedure causes.

    I'm already logging the error codes, but the associated messages are still missing.

    Isn't there any way to retrieve the message which is printed when an error arrives in Sybase?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If you're maintaining it then that suggests it worked before you changed it - I'd suggest applying one change at a time - running it and seeing if it craps out. Quite honestly it doesn't seem to difficult to look at each select into statement and see if each field has a field name - I'd guess it would take a few minutes to do at most.

  7. #7
    Join Date
    Sep 2010
    Posts
    4
    Well, that's not the only possible message. Other possible messages could come from NULL field values which shouldn't be NULL, foreign key violations and so on. I just gave that message in my first post as an example, but there could be much more than that.

    I managed at this point to check for some errors upfront; and then, if something else happens, I'll live for now with the generic Sybase error + the error code.

    However, I see error handling in Sybase stored procedures is pretty immature, to say it the harsh-less way I can. This has made this stored procedure's code, which was quite ugly, even uglier now.

    In any case, thanks a lot for your replies and insights!

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Oddly enough I rarely put error handling into my Sybase code. I know that sounds crazy to many but I just find testing the data (divide by zeros etc) before inserting it is more than enough. I think there's more bugs and limitations put into systems by the error checking than by the code - that's certainly the case for the database I'm currently having to work with. YMMV

Tags for this Thread

Posting Permissions

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