How to run system stored procedure

Hi,
After deleting the Merge Publication from Replication, Publications,
there are still remains of the publication in the database. (the
rowguid column in every table and the shared sign on the database
icon) We would like to remove them without creating a new database. It
looks like running the sp_removedbreplication is the best option
here. We are trying to run the procedure but cannot find how to. Books
online not very helpful.
Please help!
Martin
Martin,
the syntax is exec sp_removedbreplication 'yourdatabasename'. The only thing
you need to be careful of is that it will remove all traces of replication -
if this database is used as a publisher for several publications and a
subscriber also, all this will be removed. Sometimes this procedure doesn't
remove all system objects, and Hilary Cotter has previously posted up his
own script (on http://www.ava.co.uk) that will do the extra bits, but I'd
try sp_removedbreplication first.
HTH,
Paul Ibison
|||in your publication database issue a sp_replicationdboption 'merge','false'
This will get rid of most of the merge replication metadata and unshare your
database is you don't have any transactional or snapshot publications
present there. To get rid of the transactional publications issue a
sp_replicationdboption 'PublicationDatabaseName', 'publish', 'false'
sp_replicationdboption 'PublicationDatabaseName', 'merge publish', 'false'
Getting rid of the Rowguid columns is more complex.
Try this
select 'alter table ['+sysusers.name+'].'+sysobjects.name +' drop column
rowguid'+char(13)+'GO' from sysobjects, sysusers, syscolumns where
sysobjects.type='u'
and sysobjects.uid=sysusers.uid
and syscolumns.name='rowguid'
and syscolumns.id=sysobjects.id
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:3hc9j09ufhe2v7nonkpmnrbafr4i5qk011@.4ax.com...
> Hi,
> After deleting the Merge Publication from Replication, Publications,
> there are still remains of the publication in the database. (the
> rowguid column in every table and the shared sign on the database
> icon) We would like to remove them without creating a new database. It
> looks like running the sp_removedbreplication is the best option
> here. We are trying to run the procedure but cannot find how to. Books
> online not very helpful.
> Please help!
> Martin
>

How to roll back the replication?

Hi, all.
I make a db replacated as distributor.
I decided later removing replication.
But, I don't know how to remove rowguid column from all tables.
How can I set back to the initial state of DB before replication?
thank you..You can't that I know of. You will need to alter the tables and drop the columns not needed.

How to retrieve identity in stored procedure for updatable subscription??

We have transactional replication setup between SQL Server 2000 on Win2003
and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is pushing
an updatable subscription (queued updating) to the MSDE instance.
We are trying to insert master and detail rows into a pair of tables via a
stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
the ID for the child row's foreign key returns NULL, causing the child row
insert to fail. Looking at the table after running the proc, the parent row
does have a valid identity value.
Any suggestions on how to make this work?
Thanks,
Steve Klein
Steve,
I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
publisher and the subscriber).
Also, presumably this insert was done on the subscriber?
Do you have just queued or immediate updating subscribers with queued
failover?
Are there any other triggers on the table (apart from the replication ones
if we are talking about the subscriber)?
Regards,
Paul Ibison
|||I have tried to repro this and it does work. Can you post the problem proc
for us?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hbbbf92amjt6a@.corp.supernews.com...
> We have transactional replication setup between SQL Server 2000 on Win2003
> and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is
pushing
> an updatable subscription (queued updating) to the MSDE instance.
> We are trying to insert master and detail rows into a pair of tables via a
> stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
> the ID for the child row's foreign key returns NULL, causing the child row
> insert to fail. Looking at the table after running the proc, the parent
row
> does have a valid identity value.
> Any suggestions on how to make this work?
> Thanks,
> Steve Klein
>
|||Paul--
My apologies for not sending the following reply to the group. That was my
goal...
Paul--
Thanks for the response. DBCC CHECKIDENT shows no problems (publisher
and subscriber).
We don't have triggers (other than those for replication) on the tables and
there are not any other transactions affecting the transaction in our
distributed database. The test insert was done via a stored procedure on
the subscriber. This
stored procedure does the following:
1) Insert into table A
2) set @.x = @.@.IDENTITY
3) Insert into table B (using @.x as foreign key back to table A)
We have replication configured for queued updating.
Further info -- if we use IDENT_CURRENT('table_name') instead of
@.@.IDENTITY,
we do get an ID back, but IDENT_CURRENT has (claimed) global scope.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTduWcTfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Steve,
> I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
> publisher and the subscriber).
> Also, presumably this insert was done on the subscriber?
> Do you have just queued or immediate updating subscribers with queued
> failover?
> Are there any other triggers on the table (apart from the replication ones
> if we are talking about the subscriber)?
> Regards,
> Paul Ibison
>
|||Hilary--
I'll be able to post the procedure later today. Thanks.
--Steve
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have tried to repro this and it does work. Can you post the problem proc
> for us?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve Klein" <sklein@.singular.com> wrote in message
> news:10hbbbf92amjt6a@.corp.supernews.com...
Win2003[vbcol=seagreen]
> pushing
a[vbcol=seagreen]
retrieve[vbcol=seagreen]
row
> row
>
|||Steve,
there's something strange here, but I'd check to see if Scope_Identity()
also returns NULL.
Finally, I'll keep an eye on your thread with Hilary where you mentioned
posting up the script, because I'd also like to try to reproduce this.
Regards,
Paul Ibison
|||Paul--
Thanks very much for your interest. I (sheepishly) need to report that the
problem was a form of user error. The table causing the problem had a text
column. The way that the error management was implemented was evidently
causing the @.@.IDENTITY to return NULL.
Why Ident_Current() worked is odd, but since removing the text column caused
the @.@.IDENTITY to work, we are not too inclined to look much further
By the way, we have changed to Scope_Identity() rather than @.@.IDENTITY.
Thanks again.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uGr2g3qfEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Steve,
> there's something strange here, but I'd check to see if Scope_Identity()
> also returns NULL.
> Finally, I'll keep an eye on your thread with Hilary where you mentioned
> posting up the script, because I'd also like to try to reproduce this.
> Regards,
> Paul Ibison
>
|||Hilary--
Thanks for your help. As I indicated in the response to Paul's last post,
this was a stored procedure error, which I would label "user error"... Check
there for more details.
--Steve
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hg0lla3jcmu8f@.corp.supernews.com...[vbcol=seagreen]
> Hilary--
> I'll be able to post the procedure later today. Thanks.
> --Steve
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...
proc[vbcol=seagreen]
> Win2003
via[vbcol=seagreen]
> a
> retrieve
> row
parent
>
|||Steve,
many thanks for the update.
Cheers,
Paul

How to restore the entire replication topology in different boxe

We are migrating the entire sqlserver environment from Window2000 to Windows
2003 on different boxes with different NT servernames. I have installed the
sqlserver instances and I have restored the publisher, distribution db on the
primary side and the subscriber db on the replicated side. However, I don't
see any publications under the replication folder. Do I need to go through
the replication configuration first then restore the distribution?
Please advise. Thanks.
Wen,
for migrating to another server which has a different netbios name, the only
realistic solution is to script out the publications on the original setup,
install the new servers and then run the scripts on the new server, changing
the necessary parts eg servername and job owners.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)