SQL Server Home
Schema not reported in the ERROR_PROCEDURE function
4/30/2005 8:51:24 AM
User(s) can reproduce this bug
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.
SQL Server 2005 Community Technology Preview April 2005 - Developer Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
create schema testErrorSchema
create procedure testErrorSchema.testErrorMessage
select ERROR_PROCEDURE() as ERROR_PROCEDURE --, ERROR_SCHEMA()
alternatively, add a function called ERROR_SCHEMA() that will give the schema where the error was raised.
to post a comment.
Please enter a comment.
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
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
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.
on 11/5/2007 at 9:50 AM
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
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
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.
to post a workaround.
Please enter a workaround.
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...
O.name = ERROR_PROCEDURE()
on 6/19/2010 at 9:08 AM
DECLARE @Procname SYSNAME;
SELECT @Procname = schemas.name + '.' + objects.name
JOIN SYS.schemas ON SYS.objects.schema_id = SYS.schemas.schema_id
WHERE objects.object_id = @@PROCID;
STILL NO DATABASE NAME!
© 2013 Microsoft