Xp_Execresultset
by Thanh Ngo (OJ), MCDBA, MCSE, SQL MVP
On Wednesday, December 04, 2002 I posted a challenge in
MS SQLServer newsgroup to establish a feel as to how my colleages go about solving one of the
problems often encountered by SQL DBA/Developers.
Consider this scenario: You are tasked to export data (*.csv) from your master order entry table for each of your regional office.
So that each of the file can be sent to each location via FTP to be imported there. The requirement also specifies that the
filenames of our exported files must be the Office ID and the column terminator for our exported files must be a pipe ("|").
For simplicity, we are going to use the Order table in the Northwind database as our master order entry table.
The [Orders].[Employeeid] will represent our key for each of the regional offices.
Okay, let us start on solving this. The simplest route everyone including myself would do is to:
- Define a cursor for each of the region office
- Step through each key and bcp out the data for that office
The whole script would look something like below:
|
DECLARE cc CURSOR FAST_FORWARD
FOR SELECT DISTINCT Employeeid FROM Northwind..Orders
DECLARE @employeeid INT,
@sql VARCHAR(255)
OPEN cc
FETCH NEXT FROM cc INTO @employeeid
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = 'BCP "SELECT * FROM Northwind..Orders WHERE Employeeid=' + cast(@employeeid as varchar) + '" queryout "C:\Temp\' + cast(@employeeid as varchar) + '.csv" -w -t"|" -T -S"' + @@servername +'"'
EXEC master..xp_cmdshell @sql, no_output
FETCH NEXT FROM cc INTO @employeeid
END
CLOSE cc
DEALLOCATE cc
|
As you can see, the cursor route is quite simple. But how would you do this whole export task in a single T-SQL statement
and without the *drawbacks* of using cursor or while loop. Lucky for us, SQLServer (7/2K) does provide an undocumented
extended procedure (xp_execresultset). As with any undocumented stuff, please be aware that it is NOT supported by MS and
it can be changed without notice in any of MS service packs or new releases. So, you *shouldn't* use it in your critical
production environment.
With the disclaimer out of the way, let us explore the posibilities with this undocumented procedure. As the name
implies, the procedure will execute a resultset you pass to it. But how do you pass the resultset to this procedure.
Well, xp_execresultset takes 2 Unicode input parameters. The first is the *resultset* statement, and the second is the
database name in which the *resultset* statement executed under. Let us take a closer look at how it works in
the following example.
|
Use Northwind
go
-- Create a global temp table
-- to hold our SQL BCP statements
SELECT DISTINCT 'EXEC master..xp_cmdshell ''BCP "SELECT * FROM Northwind..Orders WHERE Employeeid=' + cast(employeeid as varchar) + '" queryout "C:\Temp\' + cast(employeeid as varchar) + '.csv" -w -t"|" -T -S"' + @@servername +'"''' AS sql
INTO ##TMP
FROM Orders
-- Execute the resultset
-- based on our temp table
EXEC master..xp_execresultset N'SELECT sql FROM ##TMP', N'Northwind'
-- Clean up
DROP TABLE ##TMP
go
|
Now that you see how easy it is to use xp_execresultset. Let us combine the above statements to make our single statement
that would satisfy the requirement.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout "C:\Temp\''+CAST(Employeeid AS VARCHAR)+''.csv" -w -t"|" -T -S"''+@@servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
|
So you see, xp_execresultset extended procedure is quite simple and powerful to use. It has greatly improved our performance as we no longer need to use cursor or while loop which is considered
a "no-no" in coding SQL. So, get out there and explore its potentials.
Enclosed you will also find a technique that one of our *friends*, Alejandro Mesa, uses to export data. This technique, however, does not
utitilize xp_execresultset but quite a useful technique never-the-less.
|
exec master..xp_cmdshell 'bcp "SELECT DISTINCT ''bcp """SELECT * FROM northwind..orders WHERE employeeid = '' + CAST(employeeid AS VARCHAR) + ''"""'', ''queryout """c:\temp\'' + CAST(employeeid AS VARCHAR) + ''.csv""" -T -c -t"""'' + "''|''" + ''"""'' FROM northwind..orders" queryout "c:\temp\bcp.bat" -T -c -t" " && c:\temp\bcp.bat'
|
Final note: Thanks to Steve Kass, Anith Sen, Alejandro Mesa, and all for playing!
|
News