Related to: sp_dropwebtask
One of the coolest features to demo in SQL Server 2000 is how it can automatically create a Web page based on the results of a query. The scenario is that you have a Web page that displays data from the database that doesn't change too often, such as your product catalog. You can have SQL Server automatically regenerate the page whenever any data changes, such as a new product addition or a price change, and it can even upload it to your Web server.
The magic is handled by the sp_makewebtask
, and sp_runwebtask
takes care of creating the static Web page, sp_runwebtask
runs a previously defined Web task, and sp_dropwebtask
drops a Web task. Listing 4
shows an example of the code that creates the page shown in Figure 2
. Don't despair at the industrial design of the page! You can use a page template to format the data, and there are plenty of formatting options.
|Figure 2: Industrial but Functional: This generic Web page was generated by running sp_makewebtask.|
Frankly, it is far easier to set all this up using the Web Assistant Wizard in Enterprise Manager, but it's nice to be able to write the code oneself.
Have you ever wanted to do any kind of processing for every database on a server or every table in a database? If so, the undocumented stored procedures sp_MSforeachdb
are exactly what you need. These are just about as easy to use as they can be. Simply pass each procedure a string with the T-SQL command you want to execute for each database or table:
sp_MSforeachdb "print '?' DBCC CHECKDB('?')"
sp_MSforeachtable "print '?' DBCC DBREINDEX('?')"
SQL Server substitutes each database or table name for the '?' placeholder and executes each command. Just be careful though! If you have a lot of databases or tables, commands such as DBCC
can take a long time to run.
Some of the best system stored procedures in this article are the simplest, as you've already seen by reading this far. xp_enumdsn
certainly fits into that category. It takes no parameters and returns a list of the data source names installed on the server where the code runs. This is quite handy for enumerating the DSNs instead of writing custom .NET code to do the same thing.
Related to: xp_fileexist*
It's not often that you'll need to examine the structure of the server's drive and retrieve a directory tree, but it can be handy for finding a file or deciding where to save something out to disk. The undocumented xp_dirtree
retrieves the subdirectory structure of a given drive or folder. Just pass it a fully qualified drive or directory name and you'll get back a result set with two fields: the subdirectory name and a depth field that indicates how deep its position is in the hierarchy below the specified directory.
master..xp_dirtree 'c:\Program Files'
It's not blazingly fast for a directory as large as Program Files, but it beats shelling out to the command prompt or using COM operations to get the same information.
Related to: xp_get_MAPI_default_profile*
SQL Server has supported various functions using e-mail for a long time, so it shouldn't be any surprise that it has the internal features to manipulate e-mail and check the environment for e-mail support. One of the necessary components of e-mail on the server is a MAPI profile. You have to have at least one MAPI profile installed, and you can use xp_get_MAPI_profiles
to get a list of the available profiles or xp_get_MAPI_default_profile
to get the default profile. Once you have the profile name, you can use xp_test_MAPI_profile
to test to make sure that you can use it successfully. You frequently have to deal with errors using SQLMail, and testing the profile ahead of time can avoid errors in your application.
Another simple yet useful undocumented stored procedure is xp_getnetname
. This procedure simply returns the machine name of the server, which can be useful for pointing code to the correct server.
An important part of administering any SQL Server installation is to log activity and regularly monitor those logs for problems and suspicious activity. SQL Server has plenty of options for logging, but by default it creates logs and archives them continuously. That way you have a short history of the server when anything goes wrong.
But monitoring logs can be a royal pain. While there are many third-party tools with lots of whiz-bang features that will drain your wallet, SQL Server has plenty of tools built-in. For simplicity, however, nothing beats the undocumented xp_readerrorlog
procedure. It is clearly how Enterprise Manager retrieves the list of log entries for the current log under the "Management | SQL Server Logs" section.
You can call xp_readerrorlog
without any parameters and get back the contents of the log SQL Server is currently using to log activity. Alternatively, you can pass an integer to indicate which log you want1
is the current log, 2
is the most recently archived log, and so on.
-- Read the current log
-- First archived log
One annoyance about getting the log information this way is that the first few records in the result set are header information, containing the log date, SQL Server version information, and the Microsoft copyright. If a line of text is too long (greater than about 255 characters), the output is continued to the next line and the ContinuationRow
field will contain a 1
rather than a 0
. If you use the stored procedure to create your own archive, you'll need to adjust the data to allow for these anomalies.