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.
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
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=-=>
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.
Microsoft SQL Server Engine
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.