obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
DanCreate a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> >
> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
number of indexes.
> >
> > I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages
> > Removed). However I cannot seem to find a way to do this
> >
> > Can anyone help please?
> >
> > Thanks in advance
> >
> > Dan
> >
>|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages
>> > Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> > You can't do that as the insert/exec creates a user transaction and
> > INDEXDEFRAG cannot run inside a user transaction. There's no way to
> > capture
> > the results from INDEXDEFRAG except using an external text file.
> >
> > Dan - what do you want to do with the values, out of interest?
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> >> Create a table with the same structure as the DBCC returns and then use
> > below technique:
> >>
> >> INSERT theTable
> >> EXEC('DBCC...')
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> >> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> >> > Hi All,
> >> >
> >> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> > number of indexes.
> >> >
> >> > I wish to then manipulate the values from the result set (Pages
> >> > Scanned,
> > Pages Moved, Pages
> >> > Removed). However I cannot seem to find a way to do this
> >> >
> >> > Can anyone help please?
> >> >
> >> > Thanks in advance
> >> >
> >> > Dan
> >> >
> >>
> >>
> >
> >
>