ADVERTISEMENT

FK / PK Violation - The Row Was Not Found At Subscriber When Applying Replicated Command

Aug 7, 2015

We have transactional one-way replication running, and suddenly today started getting this error :

The DELETE statement conflicted with the REFERENCE constraint "FK_BranchDetail_Branch".

The conflict occurred in database "LocationDB", table "dbo.BranchDetail", column 'BranchNumber'.

Message: The row was not found at the Subscriber when applying the replicated command.

Violation of PRIMARY KEY constraint 'PK_Branch'. Cannot insert duplicate key in object
'dbo.Branch'. The duplicate key value is (23456)

Disconnecting from Subscriber 'SQLDB03'Publisher - SQLDB02.LocationDB
Subscriber - SQLDB03.LocationDB

Tables on both servers:
Branch (BranchNumber PrimaryKey)
BranchDetail (BranchNumber ForeignKey references previous table)

select * from SQLDB02.LocationDB.Branch -- contains : 23456,'Texas',...
select * from SQLDB03.LocationDB.Branch -- contains : 23456,'NULL',...The problem is - the BranchNumber in question '23456' exists in all 4 tables (Publisher..Branch, Publisher..BranchDetail, Subscriber..Branch, Subscriber..BranchDetail).
Yet, when I ran a trace on Subscriber, I see repeated commands like:

exec [sp_MSdel_dboBranch] 23456 -- which throws FK violation
exec [sp_MSins_dboBranch] 23456,'NULL',... -- which throws PK violationI'm guessing it's trying to Update the record on subscriber by doing a Delete + Insert. But it's unable to..

Users do not have access to modify Subscriber table. But they can modify Publisher table through UI, and have been doing so for long time without issue. There is also job that updates Publisher table once every night. We started getting this error around noon today.

Our last resort is to reinitialize subscription off-hours.

View 2 Replies View Related

SQL 2012 :: 2 Replicated Objects (tables) Not Being Created On Subscriber

Sep 10, 2015

I'm in the process of migrating over nearly 900 reports to a replicated server.

I have moved over 100 reports, stored procedures and their dependent objects so far.

I have two tables that are not being applied to the subscriber.

View 9 Replies View Related

Tell When Records Are Replicated

Jul 31, 2007

Is there anyway to tell when records have been replicated to another device from SQL Server? I realize I can see it by looking in the distribution table, but I want to do it from a different database, so I cant access the distribution database from that DB. Any help would be much appreciated.
 Thanks,
Nick

View 2 Replies View Related

How To Tell When Data Has Been Replicated?

May 1, 2007

Hello,



In our environment (SQL 2005) we have a database that uses Transactional Replication to sync data between two SQL 2005 servers. There is a web app that reads/writes data to the publisher server and the other server (that gets the replicated data) is used by some other internal applications.

At times, there is a need to delete some data from the publisher server...but this can ONLY happen once the data has been successfully replicated to the second server. Is there any way to determine if a row has been replicated successfully?



thanks

- will

View 4 Replies View Related

Changes To Data Not Replicated

Jan 21, 2007

I have an application that uses web-based merge replication. My publisher is SQL 2005 and my subscriber is SQL 2005 Express. I control the replication with RMO code. If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases. However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio. What would cause these changes to not be recognized? Any ideas would be appreciated.

View 13 Replies View Related

MSSQL7 DB Replicated To MySQL DB?

Jan 4, 2000

Hi,

I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.

I have tried a few things and have had a few ideas. They are:

1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).

2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:

SELECT * from LinuxBox..MyDB.MyTable
-and-
SELECT * from LinuxBox.MyDB.MyDB.MyTable

but I get:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.


This statement:

SELECT * from LinuxBox...MyTable;

gives me:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.

A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:

Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.

Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.

3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!

Any suggestions would be greatly appreciated!

Thanks,
Greg

View 1 Replies View Related

Move Replicated Database

Sep 2, 2005

Hi,
We have to move two of our servers in US hosting SQL Server 2000 database to different location. These servers are clustered using MSCS and uses SAN to store database data. The database on this cluster hosts publisher and distributor. The other two sites in Europe host updateable subscribers. We are using transactional replication. The downtime will be around 24 hours during which servers will be physically moved from one location to other. During this time we will keep the two subscribers running and let the changes queued on subscribers. I have listed following steps based on my limited knowledge of SQL Server. Could somebody please advise if this approach is valid and if I have missed any steps?

1. Make sure no users are accessing database
2. Backup database (In case of emergency need)
3. Stop snapshot, Log Reader, Queue Reader and Distributor Agents
4. Stop SQL Server Service and SQL Agent
5. Take OS backup
5. Shutdown the server
6. Ship the servers and OS backup to new location
7. Setup servers at new location and restore OS backup
8. Start SQL Server Service and SQL Agent
9. Start agents listed in step 3

Regards,
Nilesh

View 6 Replies View Related

How To Move Replicated Database?

May 8, 2006

I got Server A with 30 Databases and I was asked to move 30 databases to Server B…
But server A is having 2 databases as Publication and Distribution to two different servers….. and Server A is a subscription for 6 Databases from other servers….

Once I move the databases from Server A to Server B…… what is the best way to get the Replications too…. I mean Server B should have 2 Db’s as Pub & Distr and 6 Db’s as Subscription…

Is there any way I can script it out in Server A and run the same script in Server B with some modification…. so that the Replication work…..??

I have Jobs and Maintenance plans…. Which should also move from Server A to Server B…. I know using DTS...... is there any better way..??

Please some one give me ur suggestion on this…

Thanks
Bob

View 6 Replies View Related

FTS Variation Between Replicated Servers??

Aug 21, 2007

I've got 2 servers and they are using replication to synch the data from one to other.
I've got a Full Text Catalog on a table on both servers.
- I repopulate the catalogs completely on both servers(now remember that the base tables of those catalogs are under replication and are exactly the same!!)
-When I run the exact same qquery against the 2 databases using the Full Text Catalogs I'd just finished rebuilding, I get a different number of results returned in my result set on one then I do on the other...
How the heck could this be possible?

View 3 Replies View Related

How To Find Out Tables Which Can't Be Replicated

Jun 22, 2004

Is there any query to find out all the tables without a Primary key or without a Unique index ?

View 3 Replies View Related

Resotring A Replicated Database

Mar 3, 2004

I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box

NOW

I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.

How about do I do that with replication being involved?

Thanx

View 2 Replies View Related

Replicated Table Is Not Updated

Jun 19, 2008

Dear All,
i've made repliaction stup using the information guide below. all the things are done axactly. now i've inserted a row in the publisher. but i'm not getting the record at the subscriber database. please let me know where i'm missing

http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related

New Column For Replicated Table

Sep 20, 2007

Hope to be my last question.
I used Transacational with update sub method. When adding new column to replicated table. Do I need to generate new snapshot again? Just want to know how can I apply the new schema to subsciber DB without doing all regenerate snapshot, recreate all tables and bulk copy.. Please help

View 2 Replies View Related

HELP! Non-Replicated DB Thinks Its A Publisher

Jul 23, 2005

I hosed myself, please help me somebody.We have a development server (SQL7) where the database is a restored copy ofproduction (where we use replication, but not in the developmentenvironment).While we rebuild the server recently (so we could move off of NT onto 2000),everything looked to be working fine.But I recently, messed up and did a massive update filling the transactionlog of the development server. Now I can't update data in development.I tried to do my usual:BACKUP LOG LAMS WITH Truncate_onlyonly to get a message:The log was not truncated because records at the beginning of the log arepending replication. Ensure the Log Reader Agent is running or usesp_repldone to mark transactions as distributed.Did a little research into BOL and tried to call the following two procs:exec sp_repldone@xactid = null ,@xact_seqno = null ,@reset = 1exec sp_repltransBoth of which gave me an error of :The database is not published.Which it isn't as we don't run replication in the development environment.I stopped and restarted the SQL Service (as that has cleared issues in thepast), but didn't reboot the server.How can I make the database not think It's replicated (or that its caughtup?).I was thinking, would I be even be able to do another restore of a morerecent copy of Production?Sincerely ...Depressed in Delaware

View 1 Replies View Related

How To Query For Records That Have Not Been Replicated

Jul 23, 2005

Hi,I am using Merge Replication for synchronizing a bunch of laptopsrunning MSDE with a central SQL Server 2000 production server.This database is used by an application that needs to know whichrecords have not yet been replicated, at any givem time.Here's a simple scenario:User A in laptop X starts his copy of the application. He syncs hisMSDE database through the application, to match the current centralservers' data. He then proceeds to add new records through theapplication and, after he is done, he synchronizes his database withthe central server, through the application.At any given time, the user might kill the application. He might addsome records today, a few more tomorrow or in a week and should be ableto change and delete records that have not yet been merged into theproduction server.The problem is that SQL Server does not have an easy way to tell whichrecords in the subscription tables have been inserted but not yetreplicated. I have been using the following query:SELECT rowguidFROM [MyTable]INNER JOIN (SELECT rowguid AS rep_idFROM msmerge_contentsWHERE generation = joinchangegen AND generation = (SELECT MAX(generation)FROM msmerge_genhistory )) dtableON rowguid_rep = dtable.rep_idThis query works for most cases but fails to identify the first recordcreated immediatly after a synchronization event.What I would like to know is if there is a correct way of identifyingrecords which are "not-yet-replicated" on a given table that is part ofa merge replication process like the one described above.Thanks in advance.

View 1 Replies View Related

Move Replicated DB To Different Server?

May 22, 2007

Is it possible to move/migrate a replicated database to a different server without breaking the subscriptions etc?

If so, is there a guide on how to do this?

Many thanks for your help in advance!

best regards, Stefan

View 3 Replies View Related

Size Of Data Replicated???

Jan 17, 2007

hi,

can anybody advice me how to find the size of the data replicated every minute. is there and procedure for getting the size of replicated data.



Thanks in advance

Jacx

View 1 Replies View Related

How To Find Out Wether A Row Has Been Replicated?

Jan 20, 2007

 

Dear ppl,

In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?

 

Regards

 

View 3 Replies View Related

Index Not Replicated To The Device

Jul 14, 2006

Hi,
I have a table published on the SQL2K Merger Replication publisher. But when I add an index to the table it's not replicated to the device. I have checked the snapshot folder and the newly added index is already in the .dri file. Any ideas?

Cheers,
Justin

View 5 Replies View Related

Rebuild Replicated Indexes

Jun 14, 2006

I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.

My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?

Any help/advice appreciated.

View 3 Replies View Related

Summary Of Replicated Records

Apr 14, 2007

Hi all

Our company is considering using replication to synchronize data between handheld devices and SQL Server 2005. One of our requirements is the ability to retrieve a summary of all updated records in the Tags table (only on the server) each time data is retrieved from one of the handhelds.

Is this easily accomplished? How can it be done?

Thanks.
-Kevin

View 3 Replies View Related

View Not Being Replicated To Device

May 4, 2007

Hello -



I have merge replication set up between a sql 2005 server and a device that has sql ce database. My original publication had only tables and this has been working fine, as far as getting data from the server (publisher) to the device (subscriber). Recently, I had a view (joins between 3 tables) that I wanted to add to the publication. When I run the replication now (even on a brand new sql ce datbase) only the tables (and their data) get replicated, the view does not get replicated to the device/sql ce database.



Is there something else I need to do to get the view to replicate to the device?



thanks

- will

View 3 Replies View Related

Altering Table Replicated

Feb 2, 2007

How can i change my Table Structure that is replicated?

I need to add a new field.

View 1 Replies View Related

Help Creating Replicated Object As Dbo.objectname

Jan 20, 2003

I just completed creating a replication job between a server at my location, where I have SA privileges, to a subscription server at a location where I have db_owner rights (no SA). When replication runs, the objects get created on the subscriber as userid.object rather then dbo.object. The subscriber permissions were set by clicking on "Replication", right clicking on "Publication", clicking on "Configure Publishing, Subscribers and Distribution...", clicking on Subscribers tab, choosing the subscriber and selecting "Use SQL Server Authentication". How do I cause replication to create the objects as dbo.object if I connect to the subscriber using an id defined as db_owner?

Thanks, Dave

View 2 Replies View Related

Alter Table In Replicated Database

Apr 26, 2003

How can I do an alter table in some table that replicated database ?
I got the error message when I try !

View 3 Replies View Related

Backup/Restore For Replicated Database

Jul 5, 2006

Hi, I have setup a merged replication on two server. Server A contains the Publisher and the Distributor database and Server B contains Subscriber.

If the database at the Subscriber got corrupted. I am thinking maybe i can 'restore' the subscriber database by resynchronising the data from the publisher to subscriber.

But what happen if the Publisher database failed? How should one restore the publisher database and then restore the replication setup?

What will be a good backup/restore strategy for such a setup?

View 1 Replies View Related

Remove Primary Key On Replicated Database

Jul 31, 2006

I have a table that was set up with a primary key - that i need to change. The problem is that the database is being replicated to a remote location, and will not allow me to remove or change the primary key on a published database.
Due to the size of the database and bandwith limitations it is not an option to re-initialise the published database.
Is there any to do this without breaking replication

View 1 Replies View Related

Changing Indexes In Replicated Tables

Dec 19, 2001

I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help

Bright

View 2 Replies View Related

Upgrading Replicated Databases To 2000

Jan 17, 2005

I am planning on upgrading one of my SQL 7 servers to SQL 2000. This server is currently and will continue to replicate with a SQL 7 server.

I want to view our current replication jobs & schedules in plan text so I can check when I recreate the replication that I haven't missed anything. Does anyone know if this is possible?

Jon

View 4 Replies View Related

Moving A Merge-replicated Database For SQL CE

Apr 6, 2004

Is there a way to move a SQL replicated database from one server to another without dropping the publication?
I have SQLCE setup with SQL server 2000; I want to redo the server but needs to move my replication to a temp server and then back.
The problem is that I do not want to drop my subscription and recreate it…it needs to stay the same.

View 2 Replies View Related

Computed Column In A Replicated Table

Apr 10, 2015

I have a strange problem. I have a computed column in a replicated table, the Formula is as follows:

(isnull(hashbytes('SHA2_256',CONVERT([varchar](256),[AccrualReference],(0))),(0))) the column is also Persisted.

This gets around a case sensitivity issue and is used as the Primary Key column, which works well.The problem is that this table is then replicated to another server. On the subscriber the value of this computed field is being returned as 0x00000000 for every row, so this must be the ISNULL function doing its job. But why? The AccrualReference is the true PrimaryKey and is never NULL.

If I remove the computed specification and set the field up as varbinary(64) the value then gets replicated. This then means maintaining a different table schema for in excess of 500 tables.

View 2 Replies View Related

Rename Replicated Table Columns

Jun 10, 2015

I need to rename a replicated table columns.will this affect my current replication (Transactional replication)? Do i have to create a new snapshot and restart the subscriber?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved