Dear all,

I can't work out with this topic. There are system sp which pull data as if
it were a report, I mean, retrieves the info without give any possibility for
the developer
to retain or to manipulate these data in a query way.
For example: sp_helprotect NULL, <user>

The solution would be open the sp.. and to copy the contents? I refuse that.

Has anyone ever used or have any experience with these stored procedures? 
Any thought/comment will be welcomed.

Regards,

You can insert the result set from that stored procedure in a table. Look up
INSERT in Books Online.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp

Example G.


Or maybe you can explain what you're actually trying to achieve.


ML

Enric,

it is possible to capture the results of the system sps where they return
only one resultset using INSERT INTO EXEC eg

CREATE TABLE #sp_helpprotect_results
    (
    owner SYSNAME,
    object SYSNAME,
    grantee SYSNAME,
    grantor SYSNAME,
    protecttype CHAR(10),
    [action] VARCHAR(20),
    [column] SYSNAME
    )

INSERT INTO #sp_helpprotect_results
EXEC sp_helprotect

SELECT * FROM #sp_helpprotect_results

DROP TABLE #sp_helpprotect_results

You can get the structure you need for the temp holding table from BOL.

For the system sps which return multiple resultsets, eg sp_help, it is
possible to manipulate them via ADO and the NextRecordset method.  I've had
some limited success with this method.

If you tell us what you want to do, perhaps there might be other
suggestions?  Let me know how you get on.


Damien


Show quoteHide quote

Thousands of thanks,


Show quoteHide quote

This article should help:

http://www.mssql.com.au/kb/html/gmgxsl.dll/htmlkb?usexsl=y&sp=psearch_article_text&@sa_id=57

You could capture the output in a table by simply defining a temp table and
then execute the command inside an insert statement.  See example below:

--drop table #test
Create table #test
(
Owner       NVARCHAR(30),
Object      NVARCHAR(100),
Grantee     NVARCHAR(30),
Grantor     NVARCHAR(30),
ProtectType NVARCHAR(30),
Action      NVARCHAR(30),
Column1     NVARCHAR(100)
)

Insert #test
EXEC sp_helprotect

Select * from #test



Show quoteHide quote

Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?
Performance question: Separate database or additional table?
Select Top @Variable * from table