Home arrow SQL Server arrow Page 2 - Extended Stored Procedures: Intro And 10 Cool Examples

Extended Stored Procedures: Intro And 10 Cool Examples

In this article Joe takes us through what an SQL Server 2000 extended stored procedure is. He also shows us how to use 10 of SQL Server 2000's most coolest extended stored procedures to accomplish tasks such as killing a system process, writing to the event log, encrypting data to a database, parsing XML, shelling to the command-line, and more.

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 134
February 11, 2002
  1. · Extended Stored Procedures: Intro And 10 Cool Examples
  2. · Extended Stored Procedures
  3. · Useful Extended Stored Procedures
  4. · Examples (contd.)
  5. · Examples (contd.)
  6. · Conclusion

print this article

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:

The properties window

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:

The properties window

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:

The Binn directory of SQL Server 2000

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:

Executing an extended stored procedure

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



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.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials