altering a column of a published table (trans repl)

Hi,
In BOL there is reference only to sp_repladdcolumn and sp_repldropcolumn
(add and drop), but nothing about altering a column, or have I missed it?
I have to change the collation of a column (that belongs to a table that is
transactionally replicated ) from sensitive to insensitive. Is the only way
to do that is to:
1 - add a New column with the correct collation (sp_repladdcolumn )
2 - copy the data from the old column into the New column (update statement)
3 - drop the old column (sp_repldropcolumn)
Any better way? or have I missed anything?
Thanks
You didn't miss anything - that's a problem we all have faced at some time
or other . Actually there's another level of iteration you missed out, as
your table will be missing the column with the oldname, so if this is to be
maintained you have to do the whole process again. There is an alternative
of dropping the subscriptions to the table, removing the table from the
publication, altering the table then readding to the publication then adding
subscriptions to this table. In this way you can effectively reinitialize on
a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
Table statement will itself be sufficient for most things.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
right, since add comes before drop, I would need 2 pairs of (add, drop ) ;
the first to alter the collation, the second to alter the column name (to
set it back to the original name) - correct?
and probably to have these 4 sp_Replxxx bracketed by Begin Tran - Commit
Tran.
is the alternative you indicated better in some ways (safer, faster, ...) ?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's potentially less processing time - largely depends on the 'width' of
your table, ie for a table not especially wide then I'd do the drop method.
if I had 200 columns, I'd do the column technique.
Cheers,
Paul Ibison
|||Thank you very much !
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#A2HWS1yFHA.464@.TK2MSFTNGP15.phx.gbl...
> It's potentially less processing time - largely depends on the 'width' of
> your table, ie for a table not especially wide then I'd do the drop
method.
> if I had 200 columns, I'd do the column technique.
> Cheers,
> Paul Ibison
>
|||Hi Paul,
Now I am in troubleshooting mode
I dropped the subscriptions to the table to alter, dropped the article (tale
from the publication), altered the table, added the article back to the
publication, and added the subscriptions to the publications. The schema
changes to the table became effective and were replicated to the destination
table (in the subscription), but the snapshot agent failed when I ran it.
The error is: The process could not create file....
I searched on MS and found a couple items (285997, 821480), but I am not
sure.
Any ideas?
The details:
I have 3 publications, each has few articles. There is one subscriber, and
only one subscription to all publications.
I did the following,
EXEC sp_dropsubscription @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.subscriber = 'SubscriberServer'
, @.destination_db = 'Dest_DB'
EXEC sp_droparticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
ALTER TABLE Orders_2 ALTER COLUMN ....
EXEC sp_addarticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.source_table = 'Orders_2'
, @.destination_table = 'Orders_2'
, @.force_invalidate_snapshot = 1
-- the next is from scripting out the publication (prior to making the
changes)
EXEC sp_addsubscription @.publication = N'Pub_2'
, @.article = N'all'
, @.subscriber = N'SubscriberServer'
, @.destination_db = N'Dest_DB'
, @.sync_type = N'automatic'
, @.update_mode = N'read only'
, @.offloadagent = 0
, @.dts_package_location = N'distributor'
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Ramadan,
please check to see if there is an automatic virus-scanner set up. If so,
disable scanning of the repldata folder.
Also, check that there is space in the distribution working folder to create
the file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
it was as simple as a missing folder could be.
Thank you very much for your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oa2$isDzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Ramadan,
> please check to see if there is an automatic virus-scanner set up. If so,
> disable scanning of the repldata folder.
> Also, check that there is space in the distribution working folder to
create
> the file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.

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;
>

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
Leila
Leila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of SQL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?
|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
> Can you show the URL(s)/article(s) where BOL says this option exists?
>
|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/pro...ads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>
|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/d...displaylang=en).
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/pro...ads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
LeilaLeila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of S
QL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-
234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/pr...oads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf2
6-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/...&displaylang=en).
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/pr...oads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
LeilaLeila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of SQL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab of
>> SQL Server 2005 setting, while BOL addresses that!
> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab of
>> SQL Server 2005 setting, while BOL addresses that!
>> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab
>> of SQL Server 2005 setting, while BOL addresses that!
>> Can you show the URL(s)/article(s) where BOL says this option exists?
>>
>