sys.sp_hexadecimal: A quasi-system object?
August 6th, 20111
sys.sp_hexadecimal: A quasi-system object?
August 6th, 20111

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 for themselves, in spite of the fact that the articles are intended only for versions prior prior to SQL Server 2008, I suggest you call it something else.


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

 USE [master];
CREATE PROCEDURE dbo.sp_hexadecimal
    PRINT 1;

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

EXEC sys.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:


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 very loud 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, as these queries all return NULL:

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

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:

    [schema]   = SCHEMA_NAME(o.[schema_id]),,
    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 = N'sp_hexadecimal';


I also can't execute the stored procedure there:

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.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

1 Response

  1. Ben Thul says:

    You're right about the big where you say that it's no longer necessary in 2008+.  Specifically, the CONVERT function has styles for binary types that outputs them in hexadecimal form.  Good post!