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.