Browse DevX
Sign up for e-mail newsletters from DevX


Two Dozen of My Favorite System Stored Procedures : Page 5

SQL Server contains a wealth of features that you can use in your own stored procedures and applications. This article explores a couple dozen of my favorite system stored procedures, including extended stored procedures and several that are undocumented.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Related to: xp_deletemail, xp_findnextmsg, xp_readmail, xp_sendmail

One of the geekier things you can do with SQL Server is to send it an e-mail in case you think it is lonely or down. Actually, you can send it a single query in the text of an e-mail and SQL Server will send the results of the query to the original sender and anyone on the cc: list as an attached file. E-mails sent to the server are processed when you run this stored procedure, so you'll probably want to run it at regular intervals to clear out the inbox, perhaps as part of a SQL Agent job.

A typical call to sp_processmail looks like the statement below. This invocation will process messages with a subject line of "SQL:pubs," return the results in a text file attachment with extension TXT, use a comma to separate the data fields, and run the queries against the pubs database.

sp_processmail @subject = 'SQL:pubs', @filetype = 'TXT', @separator = ',', @dbuse = 'pubs'

Using e-mail with a SQL Server is a fairly complicated process, normally requiring that Outlook be installed on the server—ick!—and lots of configuration to get it all working. But this can be a handy technique to execute queries against a database, particularly when you are constrained by a network configuration that prevents other ways of connecting to a database.

Despite its inherent risks, the xp_cmdshell extended system stored procedure is useful for a variety of purposes. You can use it to run any Windows command line from within a stored procedure. But be wary: leaving this stored procedure active on your server can give an attacker a dangerous tool. Keep it available only if you absolutely must use it!

Here are some examples of its usage. It is as simple as passing the command line string as the argument and optionally passing no_output if you don't want SQL Server to pay any attention to any information returned by the command.

exec master..xp_cmdshell 'dir e:\*.*' exec master..xp_cmdshell 'format j:', no_output exec master..xp_cmdshell 'format j:'

The command line executes with the security context of the SQL Server service account. So if you have the account set to Local System or any other high-privileged account, the command executes with that account's privileges.

Secure databases are critical to today's distributed applications, and SQL Server provides plenty of tools you can use to lock down the server. But making a server or database secure is a complex process, and it is far too easy to configure conflicting security settings. sp_helprotect is handy for getting information about how objects are configured for permissions in the current database.

The procedure has several options, depending on the kind of information you need. At its simplest, you can call it without any parameters and get a complete list of permissions for every object in the database. This is great for a quick survey of how things are set, but can return an overwhelming amount of information that can obscure important problems.

USE Northwind exec sp_helprotect

You can also pass in an object name and get back the permissions for just that object. In a default, unmodified version of Northwind, the following statement will show the information in Figure 1, revealing that the public role has unfettered access to the data in the Categories table. All of the columns are self-explanatory except perhaps the Column column. It contains 'all' if the setting applies to all current columns of the object, 'new' if it applies to new columns, or a combination of the two.

Figure 1: Getting Permissions: The figure shows the results after running sp_helprotect against Northwind's Categories table.

exec sp_helprotect 'Categories'

You can use the @permissionarea parameter to specify whether the results should include object permissions ('o'), statement permissions ('s'), or both.

exec sp_helprotect @permissionarea='o s'

You can also filter the results for either a specific grantor or grantee user using either the @grantorname or @username parameter.

exec sp_helprotect @username='public'

Related to: sp_releaseapplock

Transactions and object locks are a fact of life with relational databases. SQL Server has rich support for both along with many features that serve to relieve the severe concurrency problems that a heavily-used database can suffer. Most of the time you'll be in good shape using the built-in features and you don't have to worry about going deeper. But when you need to go deeper, take a look at sp_getapplock.

BOL says that this stored procedure places a lock on an application resource. That simple description belies a lot of power and complexity for a feature that I can't begin to do justice to in a short part of an article like this. But the idea is that you can create what amounts to a custom, shared or exclusive semaphore to synchronize different instances of stored procedures. Basically, if you have a weird concurrency issue you have to code around that isn't related to or solvable by built-in object locks in SQL Server, an application lock might solve the problem.

Listing 3 shows the basic idea. When creating shared locks, both instances of Query Analyzer can lock mySemaphore and do normal processing. But once a process requests and gets an exclusive lock on the semaphore, it signals to other processes that they can't do some operation until they are able to get their own lock. It's all quite similar to typical threading issues using a custom object in T-SQL.

This is another system stored procedure you won't need to use often, but when you do, it is pretty slick.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date