http://www.sqlservercentral.com/blogs/jeffrey_yao/2007/11/29/another-use-of-openrowset/ Printed 2014/10/31 10:36AM
Another use of OpenRowSet()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
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.