How to direct SP output to a file or table?

[Hansje]

So how can I send the results from a system-SP to a table or a file? I wish to run the procedure sp_publication_validation each night, because during the day it would probably be too much of a strain on an already busy DBserver. How can I capture the output in a file or a table?

Tnx,

Hans Brouwer

 

[JohnDTampaBay]

You will need to create a table (table variables will not work) with the same structure as the stored procedure result set, then INSERT...EXEC stored procedure.

CREATE TABLE PubVal (
Col1 ...
)

INSERT INTO PubVal
EXEC sp_publication_validation PubName


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Hansje]

Tnx for the response, Angel, but I tried that already and got this message for each validation output:

Generated expected rowcount value of 0 for xmliface_admin_request_response_formats_request_headers.
Server: Msg 8164, Level 16, State 1, Procedure sp_table_validation, Line 202
An INSERT EXEC statement cannot be nested.

So that does not seem to work, or do I miss something here?

 

[JohnDTampaBay]

Yuck! I was afraid that might happen. System procedures can be very complex. So much for the easy answer. Perhaps a file would be easier. Check out bcp utility, overview in BOL. Hopefully something like this will work:

bcp "sp_publication_validation PubName" out "PubVal.txt" -c -q -U"User" -P"Password"


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Hansje]

I can get it into a file allright. For trendwatching I would think it would be nice to have it in a table with date-of-check.

Tnx anyway,
Hans Brouwer