SQL Server Performance

Results of xp_cmdshell into table

Discussion in 'General Developer Questions' started by Chappy, Jan 16, 2004.

  1. Chappy New Member

    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?
  2. satya Moderator

    I'm bit confused how you can perform this using SP_ADDLINKEDSERVER.
    Normally you can store results of XP_CMDSHELL to a text file and import them using DTS or bcp etc.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Chappy New Member

    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.

  4. satya Moderator

    Thanks, thats good I think using these OPENROWSET statements this can be acheived.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Chappy New Member

    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.

  6. satya Moderator

  7. Jon M Member


    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
  8. Chappy New Member

    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
  9. Jon M Member

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

    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=':)' />
  11. Chappy New Member

    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

Share This Page