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.
Extended Stored Procedures: Intro And 10 Cool Examples - Examples (contd.) (Page 4 of 6 )
Proc #4: xp_dirtree
Sometimes it's handy to have access to the directory structure of the file system through SQL Server. The xp_dirtree is a handy extended store procedure that traverses a directory and returns the details of all of its sub-directories as records. Its signature looks like this:
To test this extended stored procedure, jump to the DOS prompt (Yes, I know it's old school, but I'm an old school kinda guy) and create some directories like this:
Enter the following line into Query Analyzer:
exec master..xp_dirtree 'c:\mydir'
After running the query, the results in the output window look like this:
As you can see from the results set above, each sub-directories name is returned along with a depth value, which is relative to the directory passed to the extended stored procedure. For example, if I passed in c:\blah and that directory contained another directory named c:\blah\blah1, then blah1's depth would be one, because it's one directory level deep.
Proc #5: sp_xml_preparedocument
The sp_xml_preparedocument extended stored procedure takes an XML document, parses it using the MSXML2 parser, and provides the parsed document, which is now ready for consumption using SQL Servers various other XML-related procedures. The signature of the sp_xml_preparedocument extended stored procedure looks like this:
The first parameter, hdoc, is an integer handle that is used to identify the XML document to SQL Server. The second parameter, xmltext, is the actual XML document that SQL Server should tell the MSXML2 library to parse, and the third option is a set of OPENXML XPath namespaces that can be used to format the resultant XML document.
To test the sp_xml_preparedocument extended stored procedure, enter the following code into Query Analyzer:
declare @id int
declare @xml varchar(500)
set @xml = '<people><person name="John Doe" age="30"/></people>'
exec sp_xml_preparedocument @id output, @xml
select * from OpenXML(@id, '/people/person')
with(name varchar(50) '@name')
exec sp_xml_removedocument @id
The code above creates a simple XML string containing a root element and one child element. We use the sp_xml_preparedocument extended stored procedure to load and parse the XML string, returning the ID of the internal representation of the XML document to @id.
To actually retrieve data from the XML, we use a normal select statement in combination with the OpenXML function, which takes the ID of the XML to load, as well as an XPath pattern that specifies the hierarchy to extract data from. The "with" expression tells SQL Server how to return the details of the XML, and the sp_xml_removedocument extended stored procedure removes the XML representation referred to by @id from memory.
The output from running the SQL batch shown above looks like this:
Proc #6: xp_availablemedia
As you might've guessed, if an SQL Server isn't setup and configured properly, then several extended stored procedures can compromise the stability of that server. The xp_availablemedia extended stored procedure as well as a couple of others can do just that.
The xp_availablemedia extended stored procedure is used to return a list of available storage devices that can be written to. It doesn't require any special signature definitions, because all you have to do to use it is call it directly, with no parameters.
Use the following line to get a list of all available storage devices:
On my SQL Server, the results set looked like this:
Proc #7: xp_getnetname
The xp_getnetname extended stored procedure returns the NetBIOS name of the current system. It accepts one optional parameter and its signature looks like this: