a extra column--rowguid.
SQL server did not clean them up.
Should I clean then one table by one table manually or there is a was to
clean them up.
Thanks
After replication done, both source database and subscription database have
a extra column--rowguid.
SQL server did not clean them up.
I delete the publication. but the column rowguid still there
Do I have to clean them one table by one table manually or there is a way
to
clean them up.
Thanks
|||This is quite normal behaviour. It may be that a (DBA) user's application
relies on these columns in some way, so it is left up to the user to remove
them. There are no inbuilt stored procs to remove these columns, but
creating yourself a script shouldn't be too difficult - syscolumns and
sysobjects are the tables you'll need. If it's just a cast of a couple of
tables I'd do it manually, but if not or if you want more of a challenge,
have a go at the script and post back if you can't do it and I'll create one
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have hundreds tables.
It will appreciate very much if you can post a sample script for this.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug15GiVvFHA.720@.TK2MSFTNGP15.phx.gbl...
> This is quite normal behaviour. It may be that a (DBA) user's application
> relies on these columns in some way, so it is left up to the user to
remove
> them. There are no inbuilt stored procs to remove these columns, but
> creating yourself a script shouldn't be too difficult - syscolumns and
> sysobjects are the tables you'll need. If it's just a cast of a couple of
> tables I'd do it manually, but if not or if you want more of a challenge,
> have a go at the script and post back if you can't do it and I'll create
one
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Chen,
I'll post it up tomorrow - what type of replication are you using?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks.
I did merge replication and I deleted the publication days ago.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGxuc$VvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Chen,
> I'll post it up tomorrow - what type of replication are you using?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||Chen,
run each of these in turn and then run the output (backup your database
first
)-- drop rowguid indexes
select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
64)=0
and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
order by sysindexes.indid
-- remove rowguid default constraints
select 'alter table ' + b.name + ' drop constraint ' + a.name from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'
-- remove rowguid columns
select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
Chen
"Paul Ibison" wrote:
> Chen,
> run each of these in turn and then run the output (backup your database
> first
)> -- drop rowguid indexes
> select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
> sysindexes
> inner join sysobjects
> on sysindexes.id = sysobjects.id
> where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
> 64)=0
> and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
> order by sysindexes.indid
> -- remove rowguid default constraints
> select 'alter table ' + b.name + ' drop constraint ' + a.name from
> sysobjects a
> inner join syscolumns on syscolumns.id = a.parent_obj
> inner join sysobjects b on syscolumns.id = b.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(b.name),'IsMSShipped') = 0
> and a.xtype = 'D'
> -- remove rowguid columns
> select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
> syscolumns
> inner join sysobjects on syscolumns.id = sysobjects.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
