output from SP to a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

output from SP to a table

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
create the table with columns exact as the output result of the SP
insert into sometable (col1, col2, . . .) exec sp_diskspace KH
Note that some system stored procedures output more than one resultset. In that case, you cannot use the INSERT syntax.
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)
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
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
can you post the query that you used ? and also what is the result that sp_diskspace return ?
KH
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.
Check the following… http://www.sommarskog.se/share_data.html
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

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
SP_DISKSPACE is the litespeed’s SP (I think) and you may need to contact Quest too. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21537 fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
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!"
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>
]]>