Extended Stored Procedures: Intro And 10 Cool Examples - Extended Stored Procedures

(Page 2 of 6 )

Before I break into discussing how to use some extended stored procedures that come pre-installed with SQL Server 2000, let's take a look at how we can view this list of stored procedures using Enterprise Manager. Start Enterprise Manager (Start -> Programs -> Microsoft SQL Server -> Enterprise Manager) and expand the nodes of the master database in the left hand column. Click on the extended stored procedures node. You should see the entire list of extended stored procedures for the master database.

Take a look at any of the other databases on your server. You'll notice that they don't have an extended stored procedures node. The only way to execute a built-in extended stored procedure is to prefix the call to the procedure with "master..", which tells SQL Server that the extended stored procedure is part of the master database.

Double click on any extended stored procedure in the right hand column that is prefixed with "sp_". Its property page will appear:



Remember earlier that I said extended stored procedures are created and loaded as DLL's? This is true for stored procedures prefixed with "xp_". As shown in the screen shot above, extended stored procedures that are prefixed with "sp_" are defined internally by SQL server, but still have access to the Win32 API's, etc as those prefixed with "xp_" do.

Close the open properties window and double click on an extended store procedure that's prefixed with "xp_". Its property page should appear:



Notice this time however that extended procedures prefixed with "xp_" show a DLL file as the path property? In SQL Server 2000, all extended stored procedures prefixed with "xp_" actually physically reside in a DLL file. To prove this, open the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder. This is the folder when the DLL's for all extended stored procedures should reside. It looks like this:



See how the xplog70.dll file exists in this directory? Whenever a call to an extended stored procedure is made as part of a batch, function, procedure, or through some third-party interface such as ADO, SQL Server will load the function from that DLL and wrap it up so that it can accept and return arguments. It's a very complex procedure, and we won't go into its details in this article.

There's one last thing to do before we look at some useful extended stored procedures, and that's how to actually call them. Fire up Query Analyzer (Start -> Programs -> Microsoft SQL Server -> Query Analyzer) and connect to any database on your server. Calling extended stored procedures is exactly the same as calling normal stored procedures, but because they exist only in the master database, each call must be prefixed with "master..", to let SQL Server know that it can find the extended stored procedure as part of the master database.

Enter the following line into the query window:

exec master..xp_fixeddrives

Click on the play button or press F5 to execute a call to the xp_fixeddrives extended stored procedure. You should get something like this:



Alternatively, you could tell SQL Server explicitly where the extended stored procedure can be found by using the USE command:

USE MASTER

GO

exec master..xp_fixeddrives

Now that we've gotten a nicely rounded intro to extended stored procedures, let's look at 10 extremely useful extended store procedures that we can use through batches, procedures and functions to improve the functionality of our code.

Next: Useful Extended Stored Procedures >>
 

More SQL Server Articles
More By Joe O'Donnell

Comments On: Extended Stored Procedures: Intro And 10 Cool Examples

>>> Be the FIRST to comment on this article!