sys.sp_hexadecimal : A Quasi-System Object?

AaronBertrand — Sat, 06 Aug 2011 05:05:00 GMT

In KB #918992 and KB #246133, which describe how to transfer logins between instances, Microsoft recommends creating a user procedure in master called sp_hexadecimal (with no schema prefix, tsk tsk). Since I know there have been a lot of people who have created this procedure in SQL Server 2008 R2 and Denali, in spite of the fact that the articles are intended only for prior versions, I suggest you call it something else. Why?

A user on twitter (@SQLDetails) asked on #sqlhelp why sp_helptext didn't work against his copy of sp_hexadecimal. Sure enough, sp_helptext does not work. I created the following procedure in master:

USE [master];
GO
CREATE PROCEDURE dbo.sp_hexadecimal
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 1;
END
GO

Executing the procedure works fine, but any variation of sp_helptext (with or without specifying the proper schema prefix) did not:

EXEC sp_helptext 'dbo.sp_hexadecimal';

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 91
There is no text for object 'dbo.sp_hexadecimal'.

OBJECT_DEFINITION() worked just fine, and gave me the text of the procedure no problem:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.sp_hexadecimal'));

So what gives? I dug into sp_helptext to see where Msg 15197 was coming from. There were two spots, but the one in question makes It seem that sp_helptext (or some underlying engine logic) ignores or overrides the specified schema prefix.This error does not occur if I create a stored procedure called sp_hexadecimal under another schema, so it seems to be isolated to the dbo schema.

Making a copy of sp_helptext in the dbo schema led to the correct rendering of the source, just like OBJECT_DEFINITION() (this is what leads me to believe there is something deeper than sp_helptext itself that causes the problem). I filed a bug against sp_helptext, Connect #683234, sp_helptext treats non-system objects as system objects. But since several of the bugs and suggestions I've filed involving this object have been closed as fixed or generated cricket sounds, I don't have a whole lot of faith it will be fixed. It's common knowledge that the sp_ system procedures are not going to be modified for new features (or any non-severe bugs).

Long story short: this all happens because sp_hexadecimal is what I call a quasi-system object.

Huh? What does that mean?

Well, it took quite a bit of unraveling, but as it turns out, sp_hexadecimal is kind of a hidden system procedure. In the master database, you can't see any evidence of its existence:

SELECT
    OBJECT_ID('sys.sp_hexadecimal'),
    OBJECT_ID('dbo.sp_hexadecimal'),
    OBJECT_ID('sp_hexadecimal');

SELECT [object_id] 
    FROM sys.all_objects
    WHERE name = 'sp_hexadecimal';

------------ ------------ ------------ 
NULL         NULL         NULL        

------------ 
NULL

There is, however, a stored procedure called sys.sp_hexadecimal in the resource database, which of course isn't visible to you under normal circumstances.

You can get to the resource database of course using the DAC (that's a Dedicated Administrator Connection, not the other DAC, a Data-Tier Application, even though now in Denali they're trying to shift the name to Diagnostic Connection for Database Administrators - so now it's a DCDA, I guess). But I like this old trick of attaching a copy of the resource database as another name. This way I don't have to remember to connect via DAC, be local on the server, etc. when I feel like sniffing around.

I loaded up a copy of the resource database, and sure enough, I found the object. But from there, the trail runs cold pretty quickly. There is a row in sys.all_sql_modules, but the definition is NULL, and OBJECT_DEFINITION() comes back empty as well:

SELECT
    o.[object_id],
    [schema]   = SCHEMA_NAME(o.[schema_id]),
    o.name,
    module_def = m.[definition],
    obj_def    = OBJECT_DEFINITION(o.[object_id])
  FROM sys.all_objects AS o
  INNER JOIN sys.all_sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE o.name = 'sp_hexadecimal';

Results:

I also can't execute the stored procedure:

EXEC sys.sp_hexadecimal;

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sys.sp_hexadecimal'.

While I don't have a whole lot of concrete answers for you, the moral of the story is, whenever you create procedures named sp_% - even when Microsoft recommends it - you could find yourself tripping over these quasi-system objects.

Search results matching tags 'metadata' and 'DAC'

sys.sp_hexadecimal : A Quasi-System Object?