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.
I have a unusual problem with a snapshot publication that I am having a hard time troubleshooting. The servers in question are all SQL 05 Enterprise. Here is the scenario:
Staging Server - This server acts as the publisher of the snapshot Production Server - This server is the subscriber of the staging snapshot Cascade Servers - These servers receive transactional updates from the production server
The staging server applies the snapshot to production successfully. From there the production server has a transactional publication which the subscribers are the cascade servers.
This model worked for a long time where we would generate the snapshot apply it to production and the updates would be sent to the cascade servers. All of a sudden there is a problem with the transactional publication from production -> cascade. There are 7-10 tables that do not receive the updates and all data in the problematic tables is truncated. The other remaining tables in the subscription receive the updates as needed. Looking at replication monitor and all other logs shows that the transactions are being replicated, but yet the 7-10 problematic tables do not contain any data. The only solution I have found for this is to recreate the entire replication model from scratch.
Any advice on how to handle this scenario? Is my model for using a transactional publication to cascade changes to a subset of servers incorrect?
The distributor was down for a long time (neer the week) and after that the commands stop applying to subscriber, while they successfully collected from publisher and stored into distributor.
Replication agent on distributor reports that initial snapshot is not available. It seems subscription is expired. But all commands from the point distributor down I have. How can I make distr agent to resume activity without reinitialize snapshot?
I have a transactional replication environment that creates subscribers on another server as a staging area for an ETL process to a data warehouse application on a 3rd server which is the report repository. Currently the ETL process runs every 10 minutes and performs it's function across approx 150+ subscriber databases and consolidates it to the data warehouse.
I have an SLA of 2 minutes. I'd like to rework the ETL process (which run as SSIS job at the moment) to be specific to a single database and fire that one ETL proces when changes have been applied to that subscriber database only. Of these 150+ databases generally only about 8-10 are updating the subscriber at any given time per Repl Monitor. I'm thinking that if I only have a few transactions to apply to a single db the ETL would run in seconds dynamically as the subscriber is update.
The issue is how to fire the ETL process upon completion of updates to the subscriber DB? I'm thinking of using SP_Start_job passing the DBID to update the warehouse but unsure whether this is possible but if so where to trigger it.
We have a filtered publisher (SQL Server 2012) with several pull and push subscriptions that have been functioning fine for years.
We have just added a new pull subscriber from a remote server. We configured an alternate folder location on the subscriber (pointing to existing path on a remote server). We generated new snapshot and partitioned snapshot, and moved to subscribing server at alternate path.We're seeing a problem we've never seen before:
When the merge agent runs, it applies the schema and performs a few hundred bulk inserts, but then proceeds to 'download changes to subscriber' as though the snapshot is old (it is brand new and should have all transactions). We have never seen this before, i.e. all data is usually applied in bulk when creating a new or reinitializing an existing subscriber.
we marked the subscriber for re-initialization (from both the publishing and subscribing server) with a fresh set of snapshots. The same problem occurred.We have also tried in our test environment (which mirrors production) and the snapshot applies as expected (via bulk inserts).
(It might be relevant to note the publisher was recently upgraded in-place from 2005 to 2012.)
I am getting "Row not found on subscriber" error on the subscriber. I am trying to find the root cause.
1. Records get lost during publication initialization from backup, could not reproduce it though. I was inserting records in a loop into a replicated table while initializing the publication from backup. After the backup/restore finished, I have stopped the insert, allowed the replication to catch up and compared the row count, it was identical.
2. Someone or some process is deleting the records on the subscriber. Setup Audit, but could not confirm this theory so far.
3. Records get deleted twice, as we publish the execution of some bulk delete archive jobs, to fix latency issues. Found this article, which says, this is not the case:
[URL]
4. Records are deleted on the subscriber by triggers. The impacted database has no triggers.
I've got a package which reads a text file into a table and updates another. I set up configurations so that I could import it into the SSIS store on both my dev and live servers. Now, I'm getting this error. I tried removing the configs and am still getting it.
I've been through each step and everything looks okay. Does anyone have any idea (a) what's wrong, (b) how to localise the error or (c) get any additional information? Or do I just have to recreate the package from scratch?
Error at PartnerLinkFlatFileImporter: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at PartnerLinkFlatFileImporter [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.
I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.
Could anybody tell me what and where is the impact when we install SP3..??
In my package i have column called "optinout" coming from source file which has the value "start" and "stop", now in my destination table i have a column called "contact permission code" to which i need to apply a business rule based on the value in column "optinout" the rule is as stated "Contact Permission Code" is OptOut if "OptInOut" value is "STOP" else OptIn.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
I having a problem importing a text file into an existing table using the DTS import wizard. The problem I am having is, I am getting an error message stating: Integrity violation: attempt to insert null data or data which violates constraints. The column the error is referenced to is an identity field and I am not trying to insert any data into the field. I have the allow insert identity box checked in the transformation section. Does anyone know about how to solve this problem. Thanks James
I have a table with 5 columns .Length of the 2nd column is 4 characters. I have to create a new database and copy the existing data.Alter the 2nd column to have 6 charactrers. Append the values in 2nd column with 00. I have only one row in the old database. I am able to copy the rows.But after that when I try to update,it is giving primary key violation error. What could be the reason?
I'm getting the following error when I run a stored procedure which truncates a table then inserts data into it. There are no complex joins, order, group by or union clauses in the insert statement:
Error: 0, Severity: 19, State: 0 SqlDumpExceptionHandler: Process 17 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I've read that this is fixed in SQL 7 SP2 but we're on SP4?? I have re-installed SQL SP4.
I keep getting a Attempt to insert duplicate key row in object... when I try to add a record. I know for a fact that the primary key is not being duplicated because I can look in the db and see that it is not there and if I continue to try to add it it will eventually succeed. Does anyone know what might be causing this?
Hi, I have got a very peculier kind of problem. My package is running on SQL 2000. There is a identity primary key in a table. Now when I submit the data from 2 different computer at the same time. Only one data is storing. The reason behind this is the primary key violation. as both the data are sending the request to the database at the same time.................n as the primary key is th identity column, it is storing one that value which is able to store the data at the forst hand. Now plz help me out in this regard................. :confused:
I am maintaining a stored proc that does the following (pseudo code for simplicity)
INSERT INTO MyTable (CountColumn, ...) VALUES (1, ...)
-- 2627 = Primary key violation IF (@@ERROR = 2627) UPDATE MyTable SET CountColumn = CountColumn + 1 WHERE ...
Basically, try to create a new record with a PK and a count of 1. If a PK violation occurs, UPDATE the count of existing record.
Pretty simple logic. On some servers this works exactly as expected; the PK violation is caught and handled internally. On other servers, this error percolates out of the stored proc and causes the calling code to receive an error and fail. All servers are SQL Server 2000 SP3a Standard Edition. Any ideas why this happens?
Is there a better (and ideally as fast or faster) way to handle the INSERT/UPDATE issue? I can put the UPDATE first and INSERT if @@ROWCOUNT is 0, but that has the slight potential of a race condition where two processes try to INSERT the same PK at the same time.
I restored same database in Publisher & subscriber.If I want to apply Transactional replication I have to apply initial snapshot.then I am getting key-violation problems during initialization .Some times Primary key table will be dropped before foreign key table.sometimes it won't able to drop some indexes.This I am getting if I am going for all tables of database.But here i need all tables.If I am going for selective table I don't have any problem.For avoiding this problem I tried all the options in table article option in publication wizard.But some na some key violation I am getting always.So please give me some better suggestion
Jobs run at 1 am , sometimes fail with following error.
Violation of PRIMARY KEY constraint 'PK__xxxxx__300424B4'. Cannot insert duplicate key in object 'VW_xxxx . [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
when I am reruning 7 am job run successfully. Any advice will be great.
Dear Friends, I am a DBA and I am getting a lot of table locks due to unhandled errors in front-end developed by Power Builder, 5 years back. The backend is SQL Server 2000 Enterprise edition. Below I will explain the problem in detail.
The Big Problem. { Ø Users enter sales data into rm_salehdr (docid,..,..,..) table (LOCKED) Ø The rm_salehdr table need a new docid Ø The new docid will be generated by a function called as docnogen Ø The docnogen function o will select the last docid from ps_docno table o add 1 to last value o return docid to rm_salehdr o update the last docid with the new value. o ps_docno table will hold the lock till the insert transaction of rm_salehdr will saved. (LOCKED) Ø If insert into rm_salehdr commit then the insert into ps_docno will be commit. (LOCK Released) Ø If insert into rm_salehdr rolled back then insert into ps_docno will be rolled back. Ø (LOCK Released)
}
The Bigger Problem { Ø At the same time if another spid select the last docno and completes it transaction before the first transaction then Primary key Violation will arise. Ø While inserting into rm_salehdr if a user get a error due to primary key violation then user will popped up with a message box explaining the primary key violation error. Ø If the user does not click the OK button then the transaction will not be ended and LOCK on the table will not released from rm_salehdr. Ø The LOCK on ps_docno will also be continued. }
The Biggest Problem {
Ø My client doesn€™t want to modify the front-end, it is very expensive for them and deployment to remote site is also a problem for them. Ø They want me to handle it from the backend. }
Is it possible that to handle the error message and release the lock on rm_salehdr from backend.
Commit the data first in ps_docno so lock will be released I want your valuable suggestions and advice.
Hi guys, ive search high and low for info regarding the "-UseInprocLoader" (in-process BULK INSERT command ) though to no avail. Their are only brief overviews on this property.
does anyone know or have detailed info on the "-UseInprocLoader" Property
I am trying to install the eval version of Reporting Services. One of the requirements for installing this is to have SQL Server 2000 with service pack 3a. I've downloaded the service pack and followed the instructions documented by Microsoft (applying SQL2KSP3.exe, then SQL2KASP3.exe).
It appears that the application of the service packs did nothing. No errors where returned after the executable was run. But, after having applied these service packs, the version has not changed. I have restarted the service, and the server with no success. The version that is returned is 'Microsoft SQL Server 2000 - 8.00.194'.
I am running MSSQL server Developer Edition, running on Windows 2000 Professional (service pack 4).
Any suggestions. I have even uninstalled the SQL server and re-applied the service packs, so I really am having trouble trying to figure out what to do next.
When I try to run SQL Server 6.5 Service Pack 5a, I consistently receive the error message
"Setup initialization could not be successfully completed. cfgchar.exe could not be executed. Please check the relevant out file."
I am currently at Service Pack 3.
1) I have insured that the PATH environment variable does not contain quotes. 2) I have no other applications running at the same time as the service pack is running.
The contents of the cfgchar.out file appear as follows:
Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?
I need to modify an already existing stored procedure, which takes three parameters. Currently it uses exclusively "or" logic to apply the parameters to a select statement. e.g.
and (adid = @adID or (editorID = @editorID and aa.editorTypeID = 1))
What I need it to do is to apply a different "and/or" logic depending on what is supplied. If two or three parameters are supplied then it needs to use "and" logic to join them. Howver, if just one parameter is supplied then it should search on that parameter alone - which it won't do if I just join them all together using "and" by default.
The work has to be done inside the T-SQL, not in the calling code. I only know two ways of doing this. Firstly to create different stored procedures and use the calling code to pick one or the other depending on supplied parameters. The other is to do if/or login inside the SP itself on the values of the parameters. Either way presents something of a maintenance nightmare. Is there any other way to do this?
Hello, I have setup web sync from wm 5.0 and it seems to crash when applying the snapshot. Looking at the column definitions it seems fine. Am I missing something? Thanks in advance.
John
The identity column must be either an integer or big integer data type and cannot be NULL. HRESULT 0x80004005 (25551)
