SQL Server Performance

output from SP to a table

Discussion in 'SQL Server 2005 General Developer Questions' started by nirukonda, May 25, 2007.

  1. nirukonda New Member

    Hi Folks,


    How to store the output generated from the stored procedure to a permanent table. What t-sql statements need to be used to insert the output of stored procedure to a permanent table, which can be viewed for later use?

    This is with reference to stored procedure sp_diskspace, which was a forum question in sql server performace .com

    I want to stored the output of sp_diskspace in to a permanent table.


    Thanks in advance
  2. khtan New Member

    create the table with columns exact as the output result of the SP


    insert into sometable (col1, col2, . . .) exec sp_diskspace



    KH
  3. Adriaan New Member

    Note that some system stored procedures output more than one resultset. In that case, you cannot use the INSERT syntax.
  4. DilliGrg Member

    quote:Originally posted by Adriaan

    Note that some system stored procedures output more than one resultset. In that case, you cannot use the INSERT syntax.

    Adriaan,
    Not clear what you mean by that? I think you can do that as Khtan has suggested, right?




    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  5. khtan New Member

    quote:Originally posted by DilliGrg


    quote:Originally posted by Adriaan

    Note that some system stored procedures output more than one resultset. In that case, you cannot use the INSERT syntax.

    Adriaan,
    Not clear what you mean by that? I think you can do that as Khtan has suggested, right?




    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

    He means like sp_help that will return multiple result sets


    KH
  6. nirukonda New Member

    Khtan,

    I did what you have suggested below, but I got the following output after ececuting the query

    Server: Msg 8164, Level 16, State 1, Procedure sp_diskspace, Line 15
    An INSERT EXEC statement cannot be nested.

    (0 row(s) affected)

    What needs to be done to avoid this error message?
    Why am I getting htis error message after execution of the query?



    Thanks in Advance

  7. khtan New Member

    can you post the query that you used ? and also what is the result that sp_diskspace return ?


    KH
  8. Adriaan New Member

    Some system sprocs already do an INSERT as part of their script, and when you exec them for an insert in your script, you get this error. No work-around available.
  9. MohammedU New Member

  10. madhuottapalam New Member

    why don't you make few modification to this sp only...

    (a) Remove the #drives temp table and replace that with your permenenant table name.
    (b) Remove Drop Table statement
    (c) Remove Create Table statement

    after these modification, whenever you run this sp, it should append the records to this table...


    Madhu
  11. satya Moderator

  12. MichaelB Member

    why on earth would they name it with the sp_ convention.. sigh.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  13. satya Moderator

    B'cos they think its easy to find... helpless habit [<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>

Share This Page