Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegr oups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----
--';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '['
+
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----
--';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

ALL 'try/catch/finally' NOT created equal?

I created a try/catch/finally but when an expection is thrown, the
catch does not handle it... (I know this code is wrong, I want to
force the error for this example)
try
{
DataSet ds = new DataSet();
string strID = ds.Tables[0].Rows[0][0].ToString();
}
catch (SqlXmlException sqlxmlerr)
{
sqlxmlerr.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(sqlxmlerr.ErrorStream);
string err =errreader.ReadToEnd();
errreader.Close();
throw new Exception (err);
}
finally
{
}
This is a ASP.NET application and when the code hits 'string strID =
ds.Tables[0].Rows[0][0].ToString();' the exception is throw and the
result below is displayed in the browser.
*************
Cannot find table 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: Cannot find table
0.
Source Error:
Line 29: {
Line 30: DataSet ds = new DataSet();
Line 31: strID = ds.Tables[0].Rows[0][0].ToString();
Line 32: }
Line 33: catch (SqlXmlException sqlxmlerr)
*************
Now, if I replace SqlXmlException with System.Exception in the
catch(), the try/catch handles they way I thought and no browser error
happens, the code goes directly to my catch (during debugging, I made
sure)....
So my question.
Just because I have a try/catch doesn't mean it will ALWAYS catch
an exception. I guess I have proven this but wanted confirmation. So
how do I know what exception class to use? Where can I find this
information.
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
You might want to post in one of the .NET NGs rather than the SQL Server XML
one. However...
The exception in your example is not a SqlException - the error is caused by
a call to a managed class (the DataSet class), so it's a System.Exception.
SqlExceptions are thrown when the server raises an exception (so for example
when an error occurs in a stored procedure you have called).
To be safe, you should generally structure your exception handling this way:
try
{
// your code
}
catch (SqlExecption se)
{
// handle SqlException
}
catch (Exception ex)
{
// handle System.Exception
}
finally
{
//clean up code
}
The idea is to create a catch clase for each exception that's likely to
occur, ordering them from more specific to more generic.
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ralph Krausse" <gordingin@.consiliumsoft.com> wrote in message
news:49eb6317.0408200638.7209f631@.posting.google.c om...
I created a try/catch/finally but when an expection is thrown, the
catch does not handle it... (I know this code is wrong, I want to
force the error for this example)
try
{
DataSet ds = new DataSet();
string strID = ds.Tables[0].Rows[0][0].ToString();
}
catch (SqlXmlException sqlxmlerr)
{
sqlxmlerr.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(sqlxmlerr.ErrorStream);
string err =errreader.ReadToEnd();
errreader.Close();
throw new Exception (err);
}
finally
{
}
This is a ASP.NET application and when the code hits 'string strID =
ds.Tables[0].Rows[0][0].ToString();' the exception is throw and the
result below is displayed in the browser.
*************
Cannot find table 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: Cannot find table
0.
Source Error:
Line 29: {
Line 30: DataSet ds = new DataSet();
Line 31: strID = ds.Tables[0].Rows[0][0].ToString();
Line 32: }
Line 33: catch (SqlXmlException sqlxmlerr)
*************
Now, if I replace SqlXmlException with System.Exception in the
catch(), the try/catch handles they way I thought and no browser error
happens, the code goes directly to my catch (during debugging, I made
sure)....
So my question.
Just because I have a try/catch doesn't mean it will ALWAYS catch
an exception. I guess I have proven this but wanted confirmation. So
how do I know what exception class to use? Where can I find this
information.
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
|||Just to add one tiny point of clarification to Graeme's explanation - the
catch block is only executed if the type of exception specified as the
parameter of the catch clause matches the type of exception that is thrown
by the code in the try block. If you specify System.Exception as the
parameter to the catch block it will catch all exceptions since every
exception in the .Net framework inherits from System.Exception.
In general, catching System.Exception can be dangerous in your code because
it means you may be catching a security exception or a stress condition like
OutOfMemoryException. I agree with Graeme that you want to catch the most
specific exception first and get more general as you go down the catch
blocks but be very careful when catching System.Exception. For more
information on best practices for exception handling check out
http://msdn.microsoft.com/library/de...guidelines.asp
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:O8Ndf$shEHA.4064@.TK2MSFTNGP12.phx.gbl...
> You might want to post in one of the .NET NGs rather than the SQL Server
XML
> one. However...
> The exception in your example is not a SqlException - the error is caused
by
> a call to a managed class (the DataSet class), so it's a System.Exception.
> SqlExceptions are thrown when the server raises an exception (so for
example
> when an error occurs in a stored procedure you have called).
> To be safe, you should generally structure your exception handling this
way:
> try
> {
> // your code
> }
> catch (SqlExecption se)
> {
> // handle SqlException
> }
> catch (Exception ex)
> {
> // handle System.Exception
> }
> finally
> {
> //clean up code
> }
> The idea is to create a catch clase for each exception that's likely to
> occur, ordering them from more specific to more generic.
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Ralph Krausse" <gordingin@.consiliumsoft.com> wrote in message
> news:49eb6317.0408200638.7209f631@.posting.google.c om...
> I created a try/catch/finally but when an expection is thrown, the
> catch does not handle it... (I know this code is wrong, I want to
> force the error for this example)
>
> try
> {
> DataSet ds = new DataSet();
> string strID = ds.Tables[0].Rows[0][0].ToString();
> }
> catch (SqlXmlException sqlxmlerr)
> {
> sqlxmlerr.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(sqlxmlerr.ErrorStream);
> string err =errreader.ReadToEnd();
> errreader.Close();
> throw new Exception (err);
> }
> finally
> {
> }
> This is a ASP.NET application and when the code hits 'string strID =
> ds.Tables[0].Rows[0][0].ToString();' the exception is throw and the
> result below is displayed in the browser.
> *************
> Cannot find table 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: Cannot find table
> 0.
> Source Error:
>
> Line 29: {
> Line 30: DataSet ds = new DataSet();
> Line 31: strID = ds.Tables[0].Rows[0][0].ToString();
> Line 32: }
> Line 33: catch (SqlXmlException sqlxmlerr)
> *************
>
> Now, if I replace SqlXmlException with System.Exception in the
> catch(), the try/catch handles they way I thought and no browser error
> happens, the code goes directly to my catch (during debugging, I made
> sure)....
> So my question.
>
> Just because I have a try/catch doesn't mean it will ALWAYS catch
> an exception. I guess I have proven this but wanted confirmation. So
> how do I know what exception class to use? Where can I find this
> information.
> Thanks
> Ralph Krausse
> www.consiliumsoft.com
> Use the START button? Then you need CSFastRunII...
> A new kind of application launcher integrated in the taskbar!
> ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
>
|||
> I created a try/catch/finally but when an expection is thrown, the
> catch does not handle it... (I know this code is wrong, I want to
> force the error for this example)
>
> try
> {
> DataSet ds = new DataSet();
> string strID = ds.Tables[0].Rows[0][0].ToString();
> }
> catch (SqlXmlException sqlxmlerr)
> {
> sqlxmlerr.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(sqlxmlerr.ErrorStream);
> string err =errreader.ReadToEnd();
> errreader.Close();
> throw new Exception (err);
> }
> finally
> {
> }
> This is a ASP.NET application and when the code hits 'string strID =
> ds.Tables[0].Rows[0][0].ToString();' the exception is throw and the
> result below is displayed in the browser.
> *************
> Cannot find table 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: Cannot find table
> 0.
> Source Error:
>
> Line 29: {
> Line 30: DataSet ds = new DataSet();
> Line 31: strID = ds.Tables[0].Rows[0][0].ToString();
> Line 32: }
> Line 33: catch (SqlXmlException sqlxmlerr)
> *************
>
> Now, if I replace SqlXmlException with System.Exception in the
> catch(), the try/catch handles they way I thought and no browser error
> happens, the code goes directly to my catch (during debugging, I made
> sure)....
> So my question.
>
> Just because I have a try/catch doesn't mean it will ALWAYS catch
> an exception. I guess I have proven this but wanted confirmation. So
> how do I know what exception class to use? Where can I find this
> information.
> Thanks
> Ralph Krausse
> www.consiliumsoft.com
> Use the START button? Then you need CSFastRunII...
> A new kind of application launcher integrated in the taskbar!
> ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
User submitted from AEWNET (http://www.aewnet.com/)
|||Well, your code is doing exactly what it says.
You coded:
catch (SqlXmlException sqlxmlerr)
which MEANS catch errors of this type [SqlXmlException] here.
The error thrown was for [System.IndexOutOfRangeException].
To catch ALL errors in on place use something like
catch(Exception e)
you can nest you catch clauses like
catch (SqlXmlException sqlxmlerr) {}
catch(Exception e){}
finianly{}
see BOL and the language specification for more details.
dlr
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23ZYEUnpSFHA.1160@.tk2msftngp13.phx.gbl...
>
> User submitted from AEWNET (http://www.aewnet.com/)

ALL 'try/catch/finally' NOT created equal?

> I created a try/catch/finally but when an expection is thrown, the
> catch does not handle it... (I know this code is wrong, I want to
> force the error for this example)
>
> try
> {
> DataSet ds = new DataSet();
> string strID = ds.Tables[0].Rows[0][0].ToString();
> }
> catch (SqlXmlException sqlxmlerr)
> {
> sqlxmlerr.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(sqlxmlerr.ErrorStream);
> string err =errreader.ReadToEnd();
> errreader.Close();
> throw new Exception (err);
> }
> finally
> {
> }
> This is a ASP.NET application and when the code hits 'string strID =
> ds.Tables[0].Rows[0][0].ToString();' the exception is throw and the
> result below is displayed in the browser.
> *************
> Cannot find table 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: Cannot find table
> 0.
> Source Error:
>
> Line 29: {
> Line 30: DataSet ds = new DataSet();
> Line 31: strID = ds.Tables[0].Rows[0][0].ToString();
> Line 32: }
> Line 33: catch (SqlXmlException sqlxmlerr)
> *************
>
> Now, if I replace SqlXmlException with System.Exception in the
> catch(), the try/catch handles they way I thought and no browser error
> happens, the code goes directly to my catch (during debugging, I made
> sure)....
> So my question.
>
> Just because I have a try/catch doesn't mean it will ALWAYS catch
> an exception. I guess I have proven this but wanted confirmation. So
> how do I know what exception class to use? Where can I find this
> information.
> Thanks
> Ralph Krausse
> www.consiliumsoft.com
> Use the START button? Then you need CSFastRunII...
> A new kind of application launcher integrated in the taskbar!
> ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
User submitted from AEWNET (http://www.aewnet.com/)Well, your code is doing exactly what it says.
You coded:
catch (SqlXmlException sqlxmlerr)
which MEANS catch errors of this type [SqlXmlException] here.
The error thrown was for [System.IndexOutOfRangeException].
To catch ALL errors in on place use something like
catch(Exception e)
you can nest you catch clauses like
catch (SqlXmlException sqlxmlerr) {}
catch(Exception e){}
finianly{}
see BOL and the language specification for more details.
dlr
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23ZYEUnpSFHA.1160@.tk2msftngp13.phx.gbl...
>
> User submitted from AEWNET (http://www.aewnet.com/)