http://www.sqlservercentral.com/blogs/jeffrey_yao/2007/11/29/another-use-of-openrowset/

Printed 2013/05/24 11:13PM

Another use of OpenRowSet()

By Jeffrey Yao, 2007/11/29

From time to time, I need to dump out the result from a SP, such as sp_lock or sp_who.

Traditionally, I will use

sp_helptext sp_lock

to get the source code and retrieve the column metadata of the result set, and then build a table based on the retrieve column information, and finall do a

insert into newly_created_table exec sp_lock

Now I simply use the following way

select * into newly_created_table from OpenRowSet('SQLCLI', 'Server=MyServer;Trusted_Connection=yes;', 'exec sp_lock')

Of course, it does not mean you can use this method to dump result set of all SPs, for example, sp_who2 is an exception.