Undocumented extended stored procedures and other helpful hints
SQL Server 6.5 offers more than meets the eye. This month we'll tell you about some undocumented gems we've discovered.
Undocumented Extended Stored Procedures
By now, everyone has at least heard about stored procedures--precompiled
collections of Transact-SQL (T-SQL) code that are stored on the server. The
names of most system stored procedures start with the three characters
sp_. System stored procedures are located in the master database; the
systems administrator owns them, and you can run them from any database. You can
also write your own stored procedures, which you usually also preface with sp_,
but you usually write them for a specific user database and store them with that
database, not in the master database.
But what are extended stored procedures? These procedures usually
have the prefix xp_. They let you extend SQL Server's functionality by
adding functions written in C. In oth-er words, extended stored procedures are
nothing more than C functions within a standard DLL that you can access from SQL
Server, using a special Open Data Services (ODS) interface. SQL Server
implements extended stored procedures as DLLs rather than as separate processes
for better performance: A separate process requires a context switch and
additional security overhead. However, the user thread executes the DLL, and the
DLL is part of the SQL Server process. Therefore, the DLL shares SQL Server's
address space and Windows NT system security privileges.
Like their stored procedure siblings, extended stored procedures support
return status codes and output parameters. In addition, SQL Server ships with
system stored procedures that add (sp_addextendedproc), drop
(sp_dropextendedproc), and provide information about (sp_helpextendedproc)
extended stored procedures.
Although we won't show you how to write extended stored procedures, we will
list all the undocumented extended stored procedures we've discovered.
Table 1
lists 16 of them that Microsoft documents in SQL Server Books Online.
The 16 extended stored procedures are nice, but the SQL Server Comprehensive
Index (in the manuals that you can order for SQL Server 6.5) references 25. In
all, 72 extended stored procedures exist, and we discovered 30 that aren't
documented anywhere.
Intrigued? So were we. Books Online quietly states, "A number of
additional extended stored procedures exist for internal use by other stored
procedures (replication, system, and so on). Although these procedures can be
executed separately, they are not guaranteed to be included in future releases."
The 30 extended stored procedures we discovered aren't hidden. Because a row
in master..sysobjects represents each extended stored procedure registered with
SQL Server, you can easily find the extended stored procedures, documented or
not, by running the following command:
SELECT name FROM master..sysobjects WHERE type = "X" order by name
This command works because "X" is the object type associated with
extended stored procedures, much as "P" is associated with plain
stored procedures and "S" with the system table. The xp_procs will
sort out at the bottom of the list and the sp_wrapper procedures will appear at
the top.
Brian created the undocumented extended stored procedures list in
Table 2
by comparing the total list with the list of extended stored procedures in Books
Online. Although the first two extended stored procedures start with sp_,
they're really extended stored procedures, even though they have an sp_ prefix.
They're stored procedure wrappers around extended stored procedures, similar to
Object Linking and Embedding (OLE) automation extended stored procedures that
have sp_ prefixes. But be warned: Extended stored procedures can affect your OS
and modify the Registry, with horrible results for your database or your NT
installation. Because we don't know how all these extended stored procedures
work, please be careful when you use them.
On Windows NT Magazine's Web site (http://www.winntmag.com), we've
taken a first pass at documenting as many extended stored procedures as we
could, but we're not sure of the calling parameters for many of them. With your
help, we'll flesh out this information and keep it updated on the magazine's Web
site. Now to our mailbag.
How can I tell SQL Server to return just the first n rows of a
result set (as you can with Microsoft Access' TOP function)?
SQL Server uses the SET ROWCOUNT n command to achieve this effect--sort of.
That command doesn't let you specify a percentage as TOP does (maybe next
version, right?), but it limits the number of rows affected by Data Manipulation
Language (DML) statements such as UPDATE, DELETE, SELECT, and INSERT.SET
ROWCOUNT n also limits the number of rows included in a keyset-driven,
server-side cursor. Note that SQL Server 4.2 didn't limit the number of rows
that ROWCOUNT affected within DML operations, so be careful if you still have
old servers lying around. The same command can produce drastically different
results on a server running SQL Server 4.2 and one running SQL Server 6.x.
SET ROWCOUNT has existed in SQL Server for a while, but SQL Server 6.5
introduced a cool twist by letting you dynamically set the value of n in
code by using a local variable. Previously, n had to be a hard-coded
value. This requirement limited its usefulness. Another word of caution when
using ROWCOUNT: Although this command limits the final result set that a query
returns, it doesn't necessarily limit the amount of work SQL Server has to do to
produce your answer. Let's assume you have a customer table with 100,000 rows,
and you issue the following T-SQL batch:
SET ROWCOUNT 1
Select * from customer order by LastName
The final result set includes only one row, but SQL Server still must sort
the entire customer table. This sort can take a very long time, depending on how
you indexed the table.
Q: My application uses @@IDENTITY , but it started returning wrong
results after I added a trigger to the underlying table. What's happening?
SQL Server 6.0 introduced the identity property, which lets you maintain
incrementing counter fields similar to an AutoNumber field in Access. SQL Server
assigns the identity value after an INSERT occurs, so programmers typically
retrieve the newly inserted value using the @@IDENTITY global variable.
The @@IDENTITY variable contains the last identity value generated for
the current connection, and you typically use the value in a syntax such as:
CREATE Table Loan (LoanId int identity, Name varchar(10), LoanAmount money)
go
DECLARE @LastLoanId int
INSERT INTO Loan VALUES('Name', LoanAmount)
SELECT @LastLoanId = @@IDENTITY
But you can't use this value if you add a trigger on the Loan Table to
perform an INSERT into another table that also contains an identity column. If
you do, SQL Server will replace the value of @@IDENTITY with the
identity value generated by the second INSERT contained within the trigger.
Currently, the problem--which can be a nightmare to troubleshoot--has no
workaround, so make sure you don't let this situation occur in one of your
databases.
Q: My application needs a custom interface for managing SQL Server users,
but I can't seem to issue sp_password through my Open Database Connectivity
(ODBC) connection. What's wrong?