Removing Merge Replication
May 22, 2007
Several years ago I set up Merge Replication on a SQL 2000 database and an MSDE Client on a Laptop. The Laptop frequently travelled out of the country and would "synch" up when it came back in.
Well the Laptop received some Viruses that we cannot get to remove so putting it on the network is not possible. Also, the owner of the laptop received a new one. He wants the database set up on the new laptop but it is not a priority at the moment and we plan to wait till the "Yemen" project is finished.
In the mean time, I want to remove the old laptop out of the Merge Replication scenario. We are not concerned about losing the data in the database on the laptop.
Does anyone know the steps I need to perform to "unreplicate" my database without being able to connect them?
View 3 Replies
ADVERTISEMENT
Apr 11, 2008
When I use the object model to try to remove a path leading to a Merge Join component, I get an error. Anybody have any workarounds? (I'm sure there is a workaround because I'm able to delete that same path via BIDS without errors.)
Though I'm trying to remove a path from a package created with BIDS, the following is an easy way to reproduce the error. The last line in the following code is the one that generates the error.
Code Snippet
Application a = new Application();
Package p = new Package();
TaskHost th = p.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
MainPipe pipe = th.InnerObject as MainPipe;
//add a connection mgr
ConnectionManager adventureWorks = p.Connections.Add("OLEDB");
adventureWorks.Name = "OLEDBConnection";
adventureWorks.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI;Initial Catalog=AdventureWorks;Data Source=(local)";
//add the OLEDB source component
IDTSComponentMetaData90 oledbSource = pipe.ComponentMetaDataCollection.New();
oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
CManagedComponentWrapper instance = oledbSource.Instantiate();
instance.ProvideComponentProperties();
oledbSource.Name = "OLEDBSource";
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID
= p.Connections["OLEDBConnection"].ID;
oledbSource.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(p.Connections["OLEDBConnection"]);
instance.SetComponentProperty("OpenRowset", "[Production].[Product]");
instance.SetComponentProperty("AccessMode", 0);
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
//add the merge join component
IDTSComponentMetaData90 mergeJoin = pipe.ComponentMetaDataCollection.New();
mergeJoin.ComponentClassID = "DTSTransform.MergeJoin";
mergeJoin.Name = "merge";
instance = mergeJoin.Instantiate();
instance.ProvideComponentProperties();
oledbSource.OutputCollection[0].IsSorted = true;
oledbSource.OutputCollection[0].OutputColumnCollection[0].SortKeyPosition = 1;
pipe.PathCollection.New().AttachPathAndPropagateNotifications(oledbSource.OutputCollection[0], mergeJoin.InputCollection[0]);
a.SaveToXml(@"c:projects est ssisSimplePipeline.dtsx", p, null);
//if I skipped the next 3 lines, I wouldn't get the error
p = a.LoadPackage(@"c:projects est ssisSimplePipeline.dtsx", null);
th = p.Executables[0] as TaskHost;
pipe = th.InnerObject as MainPipe;
int iPathToRemoveID = pipe.PathCollection[0].ID;
pipe.PathCollection.RemoveObjectByID(iPathToRemoveID); //Exception from HRESULT: 0xC0047072 (which is ErrorCode -1073450894 which is DTS_E_OBJECTIDNOTFOUND)
Some things I noticed:
1. Note the comment that says "if I skipped the next 3 lines, I wouldn't get the error". Apparently the error is somehow related to saving the package to disk then reloading it from disk. (By the way, I have to load it from disk because the package has been previously generated via BIDS, not by code. The above sample is just a simple way to repro the problem.)
2. I don't get the error with some other components (like union all). I haven't tried all the different stock components yet, but it appears to be something specific to merge join (or at least components like merge join).
I'm on SP2 proper... not any cumulative updates. (And I would like a workaround for SP2 proper.)
Thanks for any suggestions.
View 1 Replies
View Related
May 8, 2003
Hi,
We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.
How can I completely remove this whole replication from publisher and distributor?
Please help.
Thanks,
Shaili
View 5 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jun 28, 2001
Hi all,
I have a db (with replication enabled) which was marked as suspect. As per the logs it is the Hardware crash that caused this db to become suspect.
Tried with sp_resetstatus to recover the db. But it is still in suspect mode. Changed the db into emercengy mode to recover the data thru' bcp which was succesful. Since there is no other way to recover the db, now I want to drop the db and create / restore the db from backup.
I am unable to drop the db becasue it is having a publication. I am also unable to drop publication becasue now the db is in emergency mode.
Kindly help and inform how I can drop the db and create it again from the backup without reinstalling SQL Server again.
TIA.
Ravi
View 1 Replies
View Related
Jan 13, 2005
Does anyone know how to remove 'Replication Triggers'?
I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.
Any advice?
Thanks Ed
View 1 Replies
View Related
Jan 17, 2007
SQL Server 2005 Standard replicating to an identical server.
My issue:
When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.
The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.
Please Help!
View 16 Replies
View Related
Jan 21, 2008
Hi,
I'm trying to write a create database script for a test database and I need to check if the database exists before dropping and recreating it. The problem I'm having is that the test database is also merge replicating so if the db exists I need to remove the replication and then drop the database. I'm running the script using osql from a NAnt build script. The script goes something like this:
USE master
GO
if exists (select * from sysdatabases where name = N'@INITIAL_CATALOG@')
BEGIN
USE [N'@INITIAL_CATALOG@']
EXEC sp_dropmergepublication @publication = N'@PUBLICATION@'
USE master
EXEC sp_replicationdboption @dbname = N'@INITIAL_CATALOG@', @optname = N'merge publish', @value = N'false'
DROP DATABASE @INITIAL_CATALOG@
END
GO
CREATE DATABASE ...
Where the NAnt script replaces the @PARAMETER@ fields before running the script. Running the script using osql when the database doesn't exists appears to fail on parsing the line "USE [N'@INITIAL_CATALOG@']" with the error "Could not locate entry in sysdatabases for database '[DatabaseName]' ..." and does not create the database. If I run the same script in SQL Management Studio I get the same error but it does create the database.
The script should only execute the USE [N'@INITIAL_CATALOG@'] statement if the database exists, but the parser isn't able to see this and returns an error. If anyone can see an error in this script syntax, or see a different way of doing the same function without getting errors I would greatly appreciate it.
Regards,
Greg
View 4 Replies
View Related
Jul 23, 2005
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Mar 16, 2006
Hi guys does anyone know of a quick solution to remove the replication monitor icon within Enterprise Manager please I have tried all sorts but it is still appearing and starting to irritate me as I don't use replication on this server.
cheers
Jim
View 8 Replies
View Related
Aug 29, 2007
I'm trying to remove replication from my database so that I can run some update scripts on it but SQL Enterprise Manager just keeps hanging on me. I've searched the web an no-one seems to have had this problem.
Does anyone know of this problem or have they experienced it? If so, can you help?
Am I doing something wrong trying to remove the replication (deleting the publication)?
Thanks.
View 2 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Feb 14, 2002
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
View 1 Replies
View Related
Aug 12, 2002
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
View 6 Replies
View Related
Jul 31, 2000
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
View 1 Replies
View Related
Jan 22, 2001
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
View 1 Replies
View Related
Dec 19, 2000
Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....
Thanks,
Mohammed.
View 1 Replies
View Related
Sep 16, 1999
Hi all,
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
Thank you for all your time in advance.
Aziz
View 3 Replies
View Related
Sep 12, 2003
I have successfully tried merge replication on single server with 2 databases.
now i want to do the same with different servers,
when i create pull subcription on server 2 which user account should I use?
it is giving log in failure
i tried using windows admin account and also the 'sa' account.
please help me out
thanks
View 1 Replies
View Related
Feb 23, 2004
Hi All,
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
Is there any way I can make this work??
Niben
View 10 Replies
View Related
Jan 2, 2007
Hi,
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
-whitebelt
View 14 Replies
View Related
Mar 21, 2002
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] (
[PHP_Pub_ID] [uniqueidentifier] NOT NULL ,
[PHP_Data_ID] [uniqueidentifier] NOT NULL ,
[PubID] [uniqueidentifier] NOT NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pub_Year] [datetime] NULL ,
[Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD
CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY]
GO
Thanks,
Chanthol
View 3 Replies
View Related
Oct 11, 2001
Hi
Is it possible to modify or add new fields in sql 2000 when the instance
became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
Thanks
Pheckz
View 3 Replies
View Related
Oct 12, 2001
Hi everbody,
Anybody tell me about implementation of oneway merge replication.
Thanks
View 2 Replies
View Related
Feb 5, 2001
Hi everyone
SQL Server BOL says merge replication can be done in only one direction. But my understanding is Merge replication happens in both ways between publisher and subscriber.
How can i allow data movement from only wince sql ce subscribers and not from publisher running sql 2000 and win 2k.
Please reply immediately bcoz i require a solution which has to be implemented very soon.
Thanks in advance
Satheesh
View 1 Replies
View Related
Jun 29, 2004
hi,
in merge replication,i make a request subscriber with a priority 25.00,when i update the same date,it always choose the update from the publishing server,why????how does it work???
thx inadvance
View 1 Replies
View Related
Oct 5, 2004
I am using merge replication at remote connected via ISDN Dialup line. I got following error and replication fail.
publisher - PRSTGINDSQLIND
agent - PRSTGINDSQLIND-pml-pml-192.168.100.50SQLDWS-4
publication - pml
subsctription - 192.168.100.50SQLDWS:pml
error - The process could not deliver the snapshot to the Subscriber.
Agent Merge replication provider -2147201001
Agent 192.168.100.50sqldws 20037
ODBC 192.168.100.50sqldws
Agent - PRSTGINDSQLIND-pmst-pmst-192.168.100.50SQLDWS-3
error - The subscription to publication 'pmst' is invalid.
last command {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
Thanking You
R.Mall
View 2 Replies
View Related
Jan 4, 2005
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional
--------------------------------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName:ReplTestDB
Table Name :TestTable
MSDE Instance Name:SVRMYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
--------------------------------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name:LPTMYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JP
View 7 Replies
View Related
Jan 18, 2006
Hi,
I have a problem when doing merge replication. I need to have a identity column on both the publisher and subscriber, eg id. When the publisher is down, my app will now reference to the subscriber and insert into the subscriber. However when the publisher is up, the app will reference to publisher and start to insert into the publisher. This will cause conflict in the id. Also, i need the id to be in running order, therefore i cant use range for publisher and subscriber. Anyone have idea how to solve this problem?
Aaron
:(
View 2 Replies
View Related
Mar 7, 2006
Hello,
I am having a problem getting my merge replication to work out. I am wanting to make it where the merge happens on demand. I have tried to run replmerg through cmd prompt and I get the error 'The subscription to publication [namehere] has expired or does not exist.' I have had no problems running the merge agent within Enterprise Manager. I have thought of running windows sync manager but I do not want my people to have to type a password in everytime they need to sync. I am running the whole replication process through FTP. I am in desperate need of help. Anything would be great!
Thanks
View 4 Replies
View Related
Sep 14, 2006
Is it possible to setup a server as both a Publisher and Subscriber of the same database in a Merge Replication setup?
View 2 Replies
View Related
