Hi there.
There is publication with two articles with master-detail relationship.
It is very important when syncronization process start to syncronize master table first.
How can I control the order of syncronization (customized)?
Thanks.
Dears,
I need to merge data between two sql2005 servers. Could you let me know how I can do that step by step.
Thanks in advance,
I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
Hi, this is a repost from the SQL Server group.I am wondering if it is possible to change the order that articles arepropagated to the subscriber. I currently have a table, say table Athat is used in view B. However, for a variety of reasons I need tochange the name of the table to C and create a new view called A.However, view B is being replicated before view A is being replicatedand it is generating an error, "The script file for view B cannot bepropagated to the subscriber."I'm hoping that by replicating view A first I should be able to avoidthe error.Any help would be appreciated. I hope my explaination is clear enough.Thanks,Cameron
View 1 Replies View RelatedQuestion re Merge rep (pull) and processing order.  We have a group of changes associated with an app upgrade, the scripts run fine on the publisher.   Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.Â
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs.  is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
Hello
I've got the following problem: Our product is delivered with SQL2000 and SQL2005.
Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good.
But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set?
thanks for your advices.
Aline
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedHi 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).
Does this look correct for MERGE SYNTAX?I want to INSERT INTO the target table,new service contracts sold with the start date,from historical table by month, only if they don't exist in the target table, doing nothing if matched. . . but the source table is indexed Fiscal Period descending and I have multiple renewal versions of the same contract number in following years. . .
MERGE dbo.SERVICE_CONTRACTS_NEW AS Target
USING (SELECT r.Customer_Acct, r.[Contract], r.Start_Period FROM dbo.ACTIVE CONTRACTS r ORDER BY r.FiscalPeriod ASC) AS Source
ON Target.Account_Nbr = Source.Customer_Acct AND Target.[Contract] = Source.[Contract]
WHEN NOT MATCHED BY TARGET
THEN INSERT (Account_Nbr, [Contract], FiscalPeriod_Start)
VALUES (
Source.Customer_Acct
,Source.[Contract]
,Source.Start_YYYYMM
);
Hi there SQLTEAM
I have a situation; one of our clients requested us to start investigating SQL replication.
The Publisher would be the client's server running SQL7
And we would be the subscriber running SQL 2005
Can this be done?
Are there any pro's and con's on even trying replications between SQL 2005 and SQL7
If this is possible could someone be so kind as to point me to a step by step guide and how to achieve this.
I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.
Has anyone else seen an error similiar to this? Any insight would be appreciated.
Thanks in advance,
Max
I want to replicate from SQL2000 to SQL2005. I had configured distributor, publisher in SQL2000 and also subscriber in SQL2005. But so far no replication was done on SQL2005. I wonder SQL2000 (SP2) and SQL2005 replication will it work? Is it true that I should install Service Pack 3 and above in order to replicate my database to SQL2005.
Thanks
Wint
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...
Hello,
I have setup sql 2005 transactional replication system with three machines (publisher, distributor and subscriber). Couple weeks later, publisher with publications is disconnected from replication monitor (under My Publishers €“ show X on the publisher machine name). I didn€™t change any things. Can you help for this please?
Thank you for any help!!
SQL2000 used to send a 2 part update using sp_msdel followed by sp_msins instead of just calling sp_msupd. I thought this used to happen on tables with compostie primary keys. Does any know know if this still occurs in SQL2005 and maybe why?
View 1 Replies View RelatedHi All,
I like to know that in SQL2005 how many replication sides we can create for the same database. Can we create more that one are only one for each database.
1: SQL2005 Standard version Database
2: SQL2005 Enterprize version database
Thanks
I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.
The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.
Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.
Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!
Thanks in advance for any help.
Jim Youmans
St. Louis Missouri
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
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.
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!
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
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.
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
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
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
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
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
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
Hi everbody,
Anybody tell me about implementation of oneway merge replication.
Thanks
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
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
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
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