Search

Schema not reported in the ERROR_PROCEDURE function by Louis Davidson

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)
8
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.
Details (expand)
Product Language
English
Version
SQL Server 2005 Community Technology Preview April 2005 - Developer Edition
Category
SQL Engine
Operating System
Windows XP SP2 Professional
Operating System Language
English
Steps to Reproduce
create schema testErrorSchema
go
create procedure testErrorSchema.testErrorMessage
AS
begin
begin try
    raiserror ('blech',16,1)
end try
begin catch
    select ERROR_PROCEDURE() as ERROR_PROCEDURE --, ERROR_SCHEMA()

end catch
end
go
testErrorSchema.testErrorMessage

Actual Results
ERROR_PROCEDURE
--------------------
testErrorMessage
Expected Results
ERROR_PROCEDURE
--------------------
testErrorSchema.testErrorMessage

alternatively, add a function called ERROR_SCHEMA() that will give the schema where the error was raised.
Platform
 
File Attachments
0 attachments
Sign in to post a comment.
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!
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement