Thursday, May 29, 2008


Found this on usenet, courtesy of BP Margolin (who pointed at a post by Umachandar Jayachandran). I've always just figured out what the columns are and built a table, but this will save me a ton of work.

-- To enable data access locally, do:
EXEC sp_serveroption localsrvr , 'data access' , 'true'
--(where localsrvr is your server's name)

-- To do SELECT...INTO from SPs results do:
SELECT * INTO SomeTbl FROM OPENQUERY(localsrvr , '{call sp_who}')

-- If the SP uses temporary tables,
-- you have to do something like the following b'cos
-- the above call will fail
SELECT * INTO SomeTbl FROM OPENQUERY( localsrvr ,'SET FMTONLY OFF {call sp_who}')

But you need to understand that this will result in the SP being executed twice once when OPENQUERY tries to determine the metadata for the result set & again
when it actually executes the call.

No comments: