Solved

stop sql replication

Posted on 2006-06-16
11
770 Views
Last Modified: 2008-01-09
hou do i stop transactional replication after the publisher crashed  and no longer availabe
i need to be able to write to the subscriber
but it seems that i cannot because of replication
there has to be a way  help
0
Comment
Question by:dano992
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 16926222
Write to subscirber ?

don't know what you mean  when the publisher is crashed no longer replication is available. but is the publisher and the distributor the same. In that case start working on subscriber no need to worry about.

In case you are unable to make any schema changes at subscriber  (case for two way transactional replication) go to sysobjects table and make replinfo column for that table to be 0.

Note you cannot make any changes to system tables like that you have to make allow update option to be 1 for more details look for sp_configure.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16926824
Had the same problem......go to the replication, publisher
...and right click on properties you should see a page come up with subscriptions, articles etc ..go to the subscriptions...make a note of where your subscribing to which database and server...and delete them.
then go to the log reader and stop that, and stop all the agents.........think these are in the replication monitor......

If your doing snapshot..................then go to replication publisher again and readd the subscriptions there is a screen one of the options is yes to recreate the schemas something like that and just say yes and it will redo your snapshot again.....

Re start the log reader, restart the agents ...
0
 

Author Comment

by:dano992
ID: 16927345
yes the publisher and the distributer are the same server and thats the server that crashed and no longer available
the subscription is a different server and thats the one i need to be able to write to
when i try to open a table and do a return all rous and try to modify a record
it wont let me do it receive some error about the replication column on the table
so im assuming that the database is in a read only state or am i mistaken?
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16928033
yes do all that i said.....we have same set up sql1 and sql2.
so just go to the replication and delete the subscriptions stop everythng reader, and the distribution....if can't find in sql em go to the dts jobs and stop them all.....

then go back to sql replication right click on publisher....it brings up the screen with lots of tabs click on subscription delete it...then ....read add and start the snapshot you will know its working cause it will whizz through the tables.........whilst it running you can start the log reader and the subscriptions....its ok it will not start replicaiton until snapshot has run........................also the snapshot locks all tables so make sure no one on it.

just read read .......your thread...how many servers you got.......1 or 2..........if 1 is that up...and you get the errors ..........just stop everything...(all agents and all log reader, snapshots.....
the onlyway i found out to get it working was to delete the subscirptions...

you will know it has stopped go to sp_who2 your not see a thread in there......

i not got the server at home so im trying to rememeber the right buttons for you to click.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16928651
hi TRACEYMARY,
The author said "the Publisher server that crashed and no longer available"

Hi dano992 ,
No it cannot be read Only i think the error what you are getting is related to rowguid column in the replicated table so at the subscriber go to the table you are trying to modify and see if rowguid column exists there if it exists drop it

Alter Table TableName drop column rowguid
go

if this dosen't solve your problem then please post the exact error message what you are getting.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16929437
that true if only one column then you can alter table if dropping table all together your have to delete the subscriptions....and re do snapshot ...
0
 

Author Comment

by:dano992
ID: 16934409
HEERE IS THE ERROR IM RECEIVING
cannot insert the value NULL into column msrepl_tran_version
table prod_candidate column does not allow nulls
insert fails
 from what i can gather there is a replication column in everry table
and when i try to insert a row or something on the table it fails because
the data in replication row flows down from the publisher
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16934524
http://www.ureader.com/message/2762551.aspx

does the prod_candidate have a unique key........add ROWID make it indentity yes.


0
 

Author Comment

by:dano992
ID: 16934685
by the way this happens on all my tables not just prod_candidate
0
 
LVL 7

Accepted Solution

by:
TRACEYMARY earned 500 total points
ID: 16934916
Hmm......thats strange ha.......
then i stop everything and do the entire snapshot again and get the sch files re snapped.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16940033
Hi Dano992,
You configured Merge replication.

Thats why in all of your tables you are having this column. Note if you are using replication for failover then you should configrue transactional replication.

Simple solution is to drop this column from all your tables, if not assign default value newid() to all this columns


and this is how you can get rid of the column

/*
Press control + <T>
Run the below script
Take thhe result and run it again on the subscirber database
*/
set nocount on
select 'alter table '+table_name + ' drop column msrepl_tran_version' + char(13) +'go' from information_schema.columns where column_name ='msrepl_tran_version'
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question