Hi,

I created a SP to enumerate all the tables in my database. My question is : can i intercept any errors durring the DBCC command? I tested the function @@ERROR but it does not work. My filegroup was full and i got the error:

Index rebuild for AccountingGroup
Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'AccountingGroup' in database 'Demsy_Data' because the 'PRIMARY' filegroup is full.
The statement has been terminated.

I would like to determine if there is an error during the DBCC command so i can handle the error.

Thanks,
Peter.

P.S. in addition : the contents of the script

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- SQL Script : sp_DBReindex_All.sql
-- Date : 14/03/2003
-- Auteur : Peter De Cloet
-- Omschrijving : Genereren van SP die een reindex doet van alle tabellen onder Demsy_data
-- Parameters : geen
-- Returnwaarde : geen
-- Voorbeeld : use Demsy_data execute sp_DBReindex_All
--
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

use Demsy_data
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DBReindex_All]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE sp_DBReindex_All
GO


CREATE procedure SP_DBReindex_All
as


declare @name varchar(255)
declare @tablecounter int
declare @RetCode int

declare cur cursor for
select name from sysobjects where type = 'U' order by name
set @RetCode = 0

open cur
set @tablecounter = 1
fetch next from cur into @name
while @@fetch_status = 0
begin
print 'Index rebuild for ' + @name
DBCC DBREINDEX (@name)
IF @@ERROR <> 0
begin
SET @RetCode = 1
BREAK
end
-- print 'Index NOT rebuild for ' + @name
set @tablecounter = @tablecounter + 1
execute sp_user_counter1 @tablecounter

fetch next from cur into @name
end

close cur
deallocate cur

RETURN @Retcode
GO





Peter De Cloet
Systeembeheerder

DEXIA Woonkredieten
B-8800 Roeselare
Belgium

* <mailto:peter.[Email address protected]
* <http://www.dexia.be/Private_nl/Credit/Transform.asp?XML=woonkredieten.xml&XSL=credit.xsl>
* +32 (0)51 23 21 91
* +32 (0)51 23 21 61



MS Sql Server LazyDBA home page