[posted and mailed, please reply in news]
Tyler Smith Watu (wa********@yah oo.co.nz) writes:
I will like to seperate the contents of sp_dbfixedrolep ermission
into groups by the fixedrole (With the LUXURY of a heading).Is there
a any short way or straight forward command to do that?
Normally you would use a reporting tool for things like adding headers.
But assuming that you want to run this from Query Analyzer, yes there
is a way, but straightforward ? Nah...
Here is the beast:
SELECT * FROM
(SELECT * FROM
OPENQUERY (LOOPBACK,
'SET FMTONLY OFF EXEC tempdb..sp_dbfi xedrolepermissi on'))
AS h (DbFixedRole, Permission)
ORDER BY DbFixedRole
COMPUTE COUNT(Permissio n) BY DbFixedRole
The non-relational COMPUTE is the one way there is to get a header
for each group in a header. An alternative though, would be to get
data into temp table with a third column, which is char(13) + char(10)
for the last row in each group.
To run a system stored procedure in a SELECT statement I use OPENQUERY.
LOOPBACK is a linked server that points back to the local server. The
SET FMTONLY OFF was necessary, because apparently sp_dbfixedrolep ermission
uses temp tables. The FMTONLY OFF fools OPENQUERY, but the procedure
is actually ran twice. While this method can be used to run about any
stored procedure from a SELECT statement, it should be used with caution.
See
http://www.sommarskog.se/share_data.html#OPENQUERY for more details.
I also needed a derived table, so I could specify the column names.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp