Capture That Data

Test your SQL Server savvy

\[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.\]

Congratulations to Michael Hotek of, 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.


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


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?


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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.