Capture That Data

Test your SQL Server savvy

[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www .sqlmag.com/resources/email/update/main.cfm) to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.]

AND THE WINNERS ARE . . .
Congratulations to Michael Hotek of AdOutlet.com, Columbus, Ohio, and Brian Andrews of Watson Wyatt Worldwide, Washington, D.C. Michael won first prize of $100 for the best solution to the Reader Challenge. Brian won second prize of $50.

Problem

Christina knows that T-SQL is a powerful language. She's been involved in writing and enhancing various script files and stored procedures, mainly for maintenance tasks. She knows that she can do an INSERT based on the execution of a string, as in

INSERT tbl EXEC('')

But sometimes this method doesn't work. T-SQL returns the statement output in the query result window instead of in the table. She now needs to write a customized script that will defragment an index only if the index has a fragmentation level higher than a certain threshold. Christina prefers using T-SQL to write this script. How can Christina send output to a table from statements that fail to return output as a proper result set?

Solution

Some statements, such as DBCC SHOWCONTIG, don't return proper meta data with their output. Although the output looks like a result set, it's more like the output from a PRINT statement. Such output won't produce anything in the table when you use it as a subquery in an INSERT statement.

The trick here is to let osql.exe execute the statement through the xp_cmdshell extended stored procedure. For example, a statement such as

INSERT tbl EXEC master..xp_cmdshell OSQL /Q"DBCC SHOWCONTIG"

will return query output to the Windows NT console window as a result set with one variable-character column.

Alternatively, osql.exe can write the data to a file, which you can move with a bulk copy program (bcp) to SQL Server. You can then use the full power of the SELECT statement to interrogate the tbl table's contents. Extra parsing is sometimes necessary because you get only one column from xp_cmdshell (even if the statement executed by xp_cmdshell returns several columns). A script to catch information about the fragmentation level of the authors table in the Pubs database might look like Listing 1.