Prev: 46 Opinion De R4spain.com
Next: Re-Establish SQL Server 2005 Procedures
From: satyendra on 28 Oct 2009 15:32
Hello All,
I have a transactional replication using SQL 2000.
One of the tables is around 300 million records.
This has Timestamp column I am not replicating now.

Now, I need to replicate this column also.
I understand that if I change the timestamp column to binary(8), I
will be able to replicate it.

I am using a 3rd party tool for initial snapshot (backup and restore).
So, I can not use EnterPrise Manager.

I tried changing in Enterprise Manager at subscriber after the restore
is done.
It is taking lot of time (running more than 5 hours and I have other
tables as well).

What is the best way to convert timestamp to binary(8)?
Your inputs are appreciated,
Satyen
From: hilary on 31 Oct 2009 06:59

You can't directly alter a timestamp column like this:

alter table TableName alter column TimeStampColumn varbinary(8)

What you need to do and what Enterprise Manager appears to be doing is to do
something like this:
--create the original table - you won't need to to this
create table TableName(pk int identity, charcol char(20),
TimeStampColumn timestamp)
GO
--populating it with data - you won't need to do this
declare @counter int
set @counter=1
while @counter<=1000
begin
insert into TableName(charcol) values(@counter)
select @counter=(a)counter+1
end
GO
--creating the schema for a holding table - you will need to do this
select * into TableNameBak from TableName where 1=2
GO
--dropping the timestamp column
alter table TableNameBak drop column TimeStampColumn
GO
--adding the timestamp column back using the varbinary datatype
alter table TableNameBak add TimeStampColumn varbinary(8)
GO
pushing the old data into the new table
set identity_insert TableNameBak on
insert into TableNameBak (pk, charcol, TimeStampColumn)
select pk, charcol, TimeStampColumn From TableName
set identity_insert TableNameBak off
GO

GO
drop table TableName
GO
sp_rename 'TableNameBak','TableName'


You'll need to change your replication stored procedures as well.

"satyendra" <satyendrab(a)gmail.com> wrote in message
news:454e5a76-8b74-4b7f-ad5c-d93443f91464(a)z4g2000prh.googlegroups.com...
> Hello All,
> I have a transactional replication using SQL 2000.
> One of the tables is around 300 million records.
> This has Timestamp column I am not replicating now.
>
> Now, I need to replicate this column also.
> I understand that if I change the timestamp column to binary(8), I
> will be able to replicate it.
>
> I am using a 3rd party tool for initial snapshot (backup and restore).
> So, I can not use EnterPrise Manager.
>
> I tried changing in Enterprise Manager at subscriber after the restore
> is done.
> It is taking lot of time (running more than 5 hours and I have other
> tables as well).
>
> What is the best way to convert timestamp to binary(8)?
> Your inputs are appreciated,
> Satyen

 | 
Pages: 1
Prev: 46 Opinion De R4spain.com
Next: Re-Establish SQL Server 2005 Procedures