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?
ODBC connections automatically issue a few SQL Server SET commands that force the server to obey certain ANSI standards. One of the SET commands controls how SQL Server handles nulls. An offshoot of this process is that sp_password fails if you try to change a null password through an ODBC connection. Knowledge Base article Q153301 (http://www.microsoft.com/kb/default/asp) documents this behavior and concludes, "Sites should not use ODBC to issue sp_password." Brian found a simple workaround to this problem on the microsoft.public.sqlserver.server newsgroup that you might be able to use: Create a wrapper stored procedure on your server and name it sp_password_odbc:
Createproceduresp_password_odbc
@old varchar(30) = NULL --old (current) password
,@new varchar(30) --new password
,@loginame varchar(30) = NULL --login whose pw to change
as
set ansi_nulls off
exec sp_password @old, @new, @loginame
set ansi_nulls on
go
While we're talking about ODBC, don't forget the 32-bit ODBCPING utility (located in the MSSQL\BINN subdirectory). It checks whether you have installed ODBC properly.
Q: What's the purpose of the Generate Stored Procedures for Prepared Statements option in the ODBC data source name (DSN)?
When you choose the Generate Stored Procedures for Prepared Statements option, ODBC prepares user queries by wrapping them in stored procedures, and then executes the stored procedure rather than the original query. Everyone knows that stored procedures can be more efficient than ordinary queries. But we've found that the overhead involved in dynamically creating the stored procedure can offset the benefits of prepared ODBC statements. The performance degradation is more pronounced when you call the resulting stored procedure only once or twice.
Prepared stored procedures can also lead to space management problems if your application keeps connections open for long periods. Extended connections can easily happen if you're developing some type of service-based application that is always running. Under the covers, ODBC creates temporary stored procedures to implement its prepared statements. Granted, ODBC automatically drops the temp procedures when the connection closes, but what happens if your connection is from a service application that never closes? The answer is your tempdb eventually fills up, creating all sorts of problems for your application.
We suggest that you disable the prepared statement option, unless you've conducted detailed performance testing that proves the option is faster in your environment. ODBC conFigures the prepared statement option at the DSN level of each client, which is tedious for administrators if you have a large number of users. This configuration is one reason ODBC 3.0's new server-based DSNs will be nice to have.
Q: With all the talk about browser caches, I started wondering about how SQL Server uses caches. Any tips?
The sp_conFigure procedure cache setting is one of the most overlooked parameters in SQL Server tuning. This setting controls the split of memory between the data and procedure cache for the entire server. SQL Sever memory allocation is pretty straightforward. The sp_conFigure memory option controls the maximum amount of memory SQL Server can use. The SQL engine grabs some of this memory for its internal use and splits the rest between data and procedure usage, based on the procedure cache setting. A value of 30 percent for the procedure cache means that SQL Server will allocate 70 percent of the remaining memory for data usage, mostly tables and indexes. The 30 percent default setting is almost always too high, especially on servers that have 64MB or more of RAM; the setting ends up starving your table and index data of valuable memory. Unfortunately, almost every client site we've ever visited has kept the default.
We don't have enough space in the column to describe how to properly size the procedure cache, but it's easy to do using the new Procedure Cache Object, which SQL Server 6.5 adds to the NT Performance Monitor. Check out the "SQL Server-Procedure Cache Object" chapter in SQL Server Books Online for detailed information.
Q: How do I create a Seagate Crystal Reports 5.0 report with a subreport based on a SQL Server 6.5 view, and run it from Visual Basic (VB)?
If you go into the properties of Crystal Reports, you can check a box that lets you use a stored procedure as a data source for a report and pass parameters from VB. Set up a stored procedure that defines the view, and include parameters that constrain the view your application requires.
Q: How does SQL Server device mirroring relate to NT's hardware RAID?
Microsoft seems to discourage use of SQL Server device mirroring. A recent TechNet article, "Designing a Fault-tolerant SQL Server," mentions that developers implemented device mirroring when drive costs were still relatively high, and we guess SQL Server 7.0 may not support device mirroring. Hardware RAID is definitely the way to go, and Windows NT RAID is probably better tested than SQL RAID anyway. We don't know of anyone who uses the SQL RAID. If any of you have any experience with it, please let us know.
Resources
Sample data. Need sample data? Banner Software's DATAMaker
2.0 (go to http://www.datamaker.com) has generated as much as 14 million rows of
test data for one client. You can use DATAMaker to generate German and British
English data, and to test Year 2000 readiness. DATAMaker is not a new product;
it has generated test data for mainframe environments for years. DATAMaker is
now available on Windows 95 and Windows NT, starting at $650. (LogicWorks and
Bluecurve also have products that generate sample data.)
Upsizing tools. Looking for tools to upsize Access databases to SQL Server? The upsizing tools depend on which version of Access you have. Access 2.0, Access 95, and Access 97 all have different file structures, and therefore, different upsizing needs. Unfortunately, you may need more than one of the three, depending on your migration path. You can still obtain the original upsizing tool (SKU 077-051-455) for upsizing Access 2.0 to SQL Server 4.21 databases from Microsoft's fulfillment for $99. But if you have Microsoft Developer Network (MSDN) 14 or the April 1996 MSDN CD-ROM, you can access the upsizing tool for free in Product Tools & Utilities|Windows Tools|Access Upsizing Tools directory. You can find the tools for upsizing Access 95 and Access 97 databases to SQL Server 6.5 at http://www.microsoft.com/AccessDev; these tools don't have a commercial equivalent.
SQL Server Online. It finally went live. We're referring to http://www.sqlserver.com, a site that had displayed a single placeholder page with a strange warning to Netscape browser users, for more than a year. Produced by TechKnowQuest, the Web site offers forums, reviews, hints and tips, and articles about SQL Server.
Resource kit. Order this now! That's our advice on the new SQL Server Resource Kit, which Microsoft includes in the BackOffice Resource Kit, Part 2 (ISBN 1057231-5342, $129.99). The kit, including all utilities, now also ships on TechNet.
Here are two tips that grabbed us: a series of Excel macros that put a GUI wrapper around SQL Trace output and a LoadSim utility that lets you simulate running a SQL script using up to 64 concurrent connections. The kit also includes a helpful new stored procedure, sp_help_sourcetext, that addresses formatting problems encountered with the system stored procedure sp_helptext. This procedure lets you view source code stored in the syscomments table, providing you didn't save it in encrypted format. Because SQL Server stores the text as a series of 255-byte segments, sp_helptext treats the arbitrary 255-byte boundaries as new lines, breaking up the displayed source in an annoying fashion.
Don't confuse the BackOffice Resource Kit with the free SQL Server Developers' Resource Kit. You can download the latter kit from http://www.microsoft.com/sql/reskit.htm, or you can call 888-877-9085 and order part number 098-69255. The SQL Server Developers' Resource Kit is a series of white papers on topics such as Open Database Connectivity (ODBC), Object Linking and Embedding (OLE) database, and migrating to SQL Server. If you'd rather have the kit in hard copy, scroll to the bottom of the page to submit your online order. The hard copy is free, too, to US and Canadian addresses.
Enterprise Edition. Fair warning: You've heard about the forthcoming Enterprise Edition of SQL Server 6.5 that will be an interim upgrade while we wait for the Big One (SQL Server 7.0--code-named Sphinx). What you might not have heard is that the Enterprise Edition won't run on NT 3.51. One more reason to upgrade to NT 4.0, if you haven't already.
That's it for this month. Remember to send questions, tips, and, of course, fan mail! You can send them to us individually at our respective email addresses or collectively to sqlqa@winntmag.com. We'll do our best to answer your questions and will even use the best ones in future columns! Let us know if we can print your name and email address.