Home Dashboard Directory Help
Search

Schema not reported in the ERROR_PROCEDURE function by Louis Davidson


Status: 

Active


29
0
Sign in
to vote
Type: Bug
ID: 124627
Opened: 4/30/2005 8:51:24 AM
Access Restriction: Public
2
Workaround(s)
view
10
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Michael_S√łndergaard on 5/7/2014 at 2:12 PM
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.
Posted by Shawn Crocker on 4/22/2013 at 3:01 AM
Any update on this please?

The inability to determine schema is a problem if you have the same object in multiple schemas
Posted by Shawn Crocker on 12/7/2010 at 3:14 AM
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
Posted by Sacheveral on 12/27/2008 at 8:07 AM
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.
Posted by Microsoft on 11/5/2007 at 9:50 AM
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
Posted by Erland Sommarskog on 5/1/2005 at 2:08 PM
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.

Sign in to post a workaround.
Posted by Shawn Crocker on 12/7/2010 at 3:16 AM
resolve schema from sys.objects and hope you don't have the same objectname in multiple schemas...

SELECT
    SCHEMA_NAME(O.schema_id)
FROM
    sys.objects O
WHERE
    O.name = ERROR_PROCEDURE()
Posted by Tom Groszko on 6/19/2010 at 9:08 AM
DECLARE @Procname        SYSNAME;
SELECT @Procname = schemas.name + '.' + objects.name
FROM SYS.objects
JOIN SYS.schemas    ON SYS.objects.schema_id = SYS.schemas.schema_id
WHERE    objects.object_id = @@PROCID;

STILL NO DATABASE NAME!