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?
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:
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';
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.