Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Schema not reported in the ERROR_PROCEDURE function

When an error is raised by the error handler, the name of the trigger/procedure is reported by the ERROR_PROCEDURE() function. However, it does not include the schema.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 35

    <=-=May 1 2005 2:08PM=-=>

    Good catch, Louis! I will have to admit that I have not thought ot this, but you have certainly hit the nail on the head here.

    Then again, this is how the error message looks like without the catch handler:

    Msg 50000, Level 16, State 1, Procedure testErrorMessage, Line 4
    blech

    Note that only is schema missing – so is the database. And I am not really sure that it’s entirely good to add db.schema ahead of the name – there could be a compatibility issue.

    Maybe this is something for the next release.

    <=-=Nov 5 2007 9:50AM=-=>

    Hello Louis

    Thank you for your feedback. Please excuse the delayed response. I wanted to let you know that your feedback has been noted. As Erland pointed out, we would also need to consider whether to report the database name along with the schema. We will look into this further and consider providing this functionality in a future SQL Server release.

    Thanks once again.

    Sara Tahir
    Microsoft SQL Server Engine

    <=-=Dec 27 2008 8:07AM=-=>

    I have a DB with procedures in several schemas, and this is an irritation in my auditing capture. It seems a fairly simple change so I would strongly encourage it.

    <=-=Dec 7 2010 3:14AM=-=>

    If we had something like ERROR_PROCID() (an analogue of @@PROCID) then we could resolve schema using OBJECT_SCHEMA_NAME.

    As it stands, OBJECT_ID(ERROR_PROCEDURE()) fails for non-default schemas too.

    Since object name is unique per schema, we can’t reliably query sys.objects for ERROR_PROCEDURE() without having multiple hits. Although this is not expected, there could be a case when you ae migrating objects between schemas

    <=-=Apr 22 2013 3:01AM=-=>

    Any update on this please?

    The inability to determine schema is a problem if you have the same object in multiple schemas

    <=-=May 7 2014 2:12PM=-=>

    Please get this small issue fixed, then you have a lot of objects in schemas, it makes it harder to find out, which stored procedure is failing.

    <=-=Mar 6 2015 10:06AM=-=>

    I agree this is something that needs to be addressed. Schemas are useful constructs, so all functions should support them.

    <=-=Mar 6 2015 10:09AM=-=>

    I’ll also note that after 10 years and four releases, apparently this is not a high priority feature.

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base