Results of xp_cmdshell into table

Hi all,
Im trying to get the results of a call to xp_cmdshel into a memory table so I can perform further processing before returning the results to the caller of the stored proc.

Im aware I can do this using sp_addlinkedserver, but the login that this stored procedure will run under does not have permissions to be creating linked servers, and id rather keep it that way.

Does any one have any further suggestions please?

Sorry, I should have been more clear.

What I meant to say was I was aware of the method to do this using OPENQUERY (after adding the server to itself using sp_addlinkedserver). This is a nice idea vbkenya had on threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1001

I have hit on a solution to use OPENROWSET instead of OPENQUERY as this allows ad-hoc access without linking the server and is suitable for my needs.

Regards

Thanks satya, but I think I was a bit premature..



SELECT a.* FROM OPENROWSET('SQLOLEDB','CAPNET'; 'sa';, 'EXEC master..xp_cmdshell ''type E:ScriptTmp.txt''') AS a

This is the statement that is now built dynamically.
When I try to execute this, even in query analyser, I get the error..



Could not process object 'EXEC master..xp_cmdshell 'type E:ScriptTmp.txt''. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

:-(

Chappy,

If your objective is just to capture the rows returned by this,
EXEC master..xp_cmdshell 'type E:ScriptTmp.txt'

then you may create a table then perform this sql:

INSERT INTO mytable
EXEC master..xp_cmdshell 'type E:ScriptTmp.txt'

All rows should be saved in mytable.

Jon M

Hehe, lovely, thanks. Im not sure why I was trying such a convoluted method to do this.. I was pretty sure exec into a table wouldnt work (so convinced I didnt even try it!)

Thanks again

me neither, i just tried this today [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Jon M

You were probably confusing insert into table/#table exec... with insert into @table exec... the last one won't work. You did mention in your first post a memory table (datatype table, I assume) <img src='/community/emoticons/emotion-1.gif' alt='' />

Ah yes, very true. But by the time I came round to trying Jons suggestion I had switched to a #temp table in the course of playing around <img src='/community/emoticons/emotion-1.gif' alt='' /><br />Temp table will suit me fine as it happens. The process does not need to perform brilliantly.<br /><br />Thanks everyone for the help