Hi, everyone,
I have problems to setup peer-to-peer replication. After I setup, I have an error message as below.
Violation of PRIMARY KEY constraint 'XPKSYS_SRTY_ADT'. Cannot insert duplicate key in object 'dbo.SYS_SRTY_ADT'. (Source: MSSQLServer, Error number: 2627)
Event ID 14151 in vent viewer.
Does anyone have a solution to resolve the problem?
Thanks a lot.
Hello,
I am getting the following message when I am trying to insert into my table. Request is the primary keys and the Bus_Req_Id and Test_Case_Id are both foreign keys. The data that is being inserted into these fields are not unique.
This is the exact error message:
Violation of PRIMARY KEY constraint 'PK_REQUEST_BUS_REQ_TST'. Cannot insert duplicate key in object 'REQUEST_BUS_REQ_TST'.
The statement has been terminated.
I am trying to insert 700 - 900 rows based on certain criteria. If I delete the keys the inserts will work.
Any help would be most greatful.
Thank you in advance
Anne
We are using web application, our server log this type error. some can help me out how to slove this type of error
4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555
4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [ADA.1.316] Cannot execute the SQL statement "INSERT INTO db_webmethods_support_edc.dbo.tbl_edc_state(stat_s tate_id, state_item_id, state_start_ts) VALUES (?, ?, ?)". "
(23000/2627) [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'.
(HY000/3621) [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The statement has been terminated."
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555
4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555
I have a constraint on a 3rd party app that I can't change the app but can add a constraint at the table level. The constraing works but the error message back is ugly. Is there a way to do some kind of raiserror on a constraint that could send better info back to the user, I have done this in triggers, is the an approach for constraints?
View 3 Replies View Relatedi need the t-sql statement for a check constraint that has a custom error message that shows the proper fromat for column entry.... i know its confusing...
example... if im using the authors table in the pubs database and the numbers in the au_id column reads xxx-xxxxxx where x represents numbers... what must i do so that the error message reads the proper format... ie. use proper format XXX-XXXXXX, is what i might want the error message to read.
thanks in advance
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedI have a SQL server scheduled to replication. This works fine if it has to do that one the sever on the same mechine. If it has to be done to another server, the replication fails with the following error message.
08001[Microsoft][ODBC SQL Server Driver][dbnmpntw]Connectionopen(Createfile())
Does any one know how to fix this.
Any help will be appreciated.
Thank you.
Hi all SQL Replication experts :)
I have created my production server as a distributor and a publication on that server. On my backup server I have created a pull subscription. After that I have generated SQL scripts on my backup server so I can create the pull subscription anytime.
To test my script I used sp_removedbreplication 'dbname' to remove replication on the backup server. I then used the generated SQL script to create the pull subscription again. I got this error message
Job 'ProdServName_DBName-BackServName-DBName401A48AE-D8DC-4F29-A610-13916370CD0B' started successfully.
Server: Msg 208, Level 16, State 1, Procedure sp_addsubscription, Line 135
Invalid object name 'syspublications'.
What does this Error message mean and what can I do about it?
Grateful for all answers
Best,
/M
I have a publication that has been working great for over eight months. I have on occassion had to stop publishing and stop subscribing in normal operation without problems.
Yesterday, I ran into a problem that I am not sure how to solve.
I keep getting a distribution task failure "Violation of Primary Key constraint 'aaaashipm_pk'. Attempt to insert duplicate key in object shipm failed while bulk copying into shipm
Anyone run across this or have any suggestions -- do I have a transaction log problem, distribution data base/log problem. I have checked the identity column to make sure its not out of sync, run a check on the table to make sure its not corrupted. I have a couple of other things I am trying but was curious if anyone else had come across this?
Will let you know if I find a solution but it doesn't look good.
Hello,
We are consistently getting the error message below on our subscribers that have blob images. Is there a way to increase a setting to avoid SQL to throw this error, or another suggestion? Thanks in advance.
John
Error messages:
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.
18 -BcpBatchSize 100000
18 -ChangesPerHistory 100
18 -DestThreads 2
18 -DownloadGenerationsPerBatch 5
18 -DownloadReadChangesPerBatch 100
18 -DownloadWriteChangesPerBatch 100
18 -FastRowCount 1
18 -HistoryVerboseLevel 3
18 -KeepAliveMessageInterval 300
18 -LoginTimeout 15
18 -MaxBcpThreads 2
18 -MaxDownloadChanges 0
18 -MaxUploadChanges 0
18 -MetadataRetentionCleanup 1
18 -NumDeadlockRetries 5
18 -PollingInterval 60
18 -QueryTimeout 400
18 -SrcThreads 2
18 -StartQueueTimeout 0
18 -UploadGenerationsPerBatch 3
18 -UploadReadChangesPerBatch 100
18 -UploadWriteChangesPerBatch 100
18 -Validate 0
18 -ValidateInterval 60
Due to the memory leak issue with respect to replicating blobbed images we have changed UploadGenerationsPerBatch to = 3.
Hi,
I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.
The replication share on the distributor has all the generated DDL in it.
I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error
The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)
Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?
The ddl for the table index is as follows ( from the replication folder)
/----
CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)
GO
---/
/------ Keys ddl (.dx)
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go
--------/
/------------
Command attempted:
create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@c1 int,@c2 varchar(80),@c3 char(2),@c4 char(3),@c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @c2
,"ISO 3166-1 ALPHA-2" = @c3
,"ISO 3166-1 ALPHA-3" = @c4
,"ISO 3166-1 NUMERIC-3" = @c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@c2
,@c3
,@c4
(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)
------------/
Hi,
I am trying to insert into a table. I got the above error message then I deleted all the data in the table. But I am still getting this error.
Whats wrong?
I know what this error means is that you can not insert duplicate primary keys in the table but the thing is I am checking the rows if they do not exist then insert otherwise don't do any thing these are the lines I am writing in my strored procedure can someone please let me know what I am doing wrong here.
If not exists
(Select * From GGP WHERE
FFECTIVE_DATE =@v_EFFECTIVE_DATE AND
ASSET_ID= @v_ASSET_ID AND
ASSET_TYPE = @v_ASSET_TYPE AND
Value = @v_Value AND
hour = @v_Hour)
INSERT INTO GGP
(ASSET_ID,ASSET_TYPE,Hour,Value,EFFECTIVE_DATE) values(@v_ASSET_ID,@v_ASSET_TYPE,@v_Hour,@v_Value, @v_EFFECTIVE_DATE)
The exact error is
Violation of PRIMARY KEY constraint 'PK_SP_GGP'. Cannot insert duplicate key in object
Thanks in advance.
When I try to insert data into a table (let's just call it MyTable for this post), I suddenly get the following error.
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'MyTable'.
My table does have a primary key field named 'id', which is an auto-incrementing BIGINT. The last record I successfully inserted received 14 in the id field, so I'm assuming the database is trying to assign 15 to the next. Unfortunately, there is already a record with an id of 15; the next available id is 21.
Is there a way to avoid these primary key collisions?
Hi All,
I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:
alter table databasesize
add constraint pk_dbsize primary key (dbid)
But I get the error message:
Table 'databasesize' already has a primary key defined on it.
How can I do this?
I have an odd problem on something that used to work fine.
I have an SP that inserts a record into a table (Contract) with two keyed fields.
The keys are as follows:
ContractID and SeqID (Sequence)
These two keys make the records unique.
Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc....
Several weeks of using this procedure have been fine. Suddenly I started getting this error:
Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.
I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.
Has anybody experienced this before?
Any help would be apprciated!
Here is the code in the SP;
CREATE PROCEDURE bcipNewContractSeq @ContractID Char(10 )AS
DECLARE @MaxSeqID int
DECLARE @NewSeqID int
SELECT @MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @ContractID
SET @NewSeqID = @MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @ContractID, @NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @ContractID
GO
I'm trying to insert records into a table and getting the below error and not sure how to resolve it:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_propertyItem'. Cannot insert duplicate key in object 'propertyItem'.
The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_tbl_others. Cannot insert duplicate key in object 'tbl_others.
The statement has been terminated.-- What does it mean i cant load the data to tbl_others
Hi allWould there be a easy way to find the column name(s) which constitutea Primary constraint for a table through navigating the systemcatalogs.I found that the PK Constraint object in syscontraints is showing thecolid = 0.TIANorman
View 2 Replies View RelatedWhen replication merge, is taking place between two servers on one particular table I keep getting violation of primary key constraint, Does anyone out there have a fix for this?
View 1 Replies View RelatedHi,
I've a set of source tables on one server and a set of destination tables on another.
When I move data from one server to next, I get an error saying "Violation of Primary Key Constraint". This happens for only one table among the 6 tables that I have. I dont understand why it throws me this error. Though theres an error for this table, the data from source table moves into this destination table.
Am I missing something?
Thanks,
Subha Fernando
Hi,
I've a set of source tables on one server and a set of destination tables on another. I'm moving this using a data flow task.
When I move data from one server to next, I get an error saying "Violation of Primary Key Constraint". This happens for only one table among the 6 tables that I have. I dont understand why it throws me this error. Though theres an error for this table, the data from source table moves into this destination table.
Am I missing something?
Thanks,
Subha Fernando
I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?
View 10 Replies View RelatedGot the following error when the backup was run
Executing the query "BACKUP DATABASE [msdb] TO DISK = N'd:Sql backups..." failed with the following error: "Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB7WERB85D3'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (16771).Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
12 percent processed.
21 percent processed.
31 percent processed.
41 percent processed.
[code]...
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi
View 2 Replies View RelatedI am running merg replication (pull) between 7 various sites and once in a while when two people update information in a table in close proximty in time, I will get a primary key violation when viewing conflicts, it's almost excluslively in one table. What is the best way to handle this error. Should I delete the entire column on the subscriber and let the publisher update the subscriber? Or should I redesing the table and primary keys? Any assistance in the matter would be greatly appreciated.
View 1 Replies View Relatedselect * from sysobjects where type = 'K'
name
------
pk_dtproperties
1 row(s) affected)
When I say - DROP CONSTRAINT pk_dtproperties
i get this error
-------------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONSTRAINT'.
NOTE:
In tables, I don't have any tables all tables i deleted but wonder from where this creature comes from,.........
Hey there :)I'm using Ruby on Rails to create a migration of a legacy database onan MS SQL Server. I'd like to name my constraints myself, such as'pk_authors', but in the cases where a table has an auto incrementedIDENTITY(1,1) id field, Rails takes over and adds the primary keyitself when creating the table.This is fine, except then the constraint gets a name like'PK__authors_384934' which is not very intuitive or easily remembered;)I'd like to just throw in an 'ALTER TABLE authors...' statement justafter that table has been created, but I'm not sure how to go aboutrenaming the auto generated constraint since the name it gets ispartially random. Is there any way to indirectly refer to theconstraint like 'RENAME PRIMARY KEY CONSTRAINT ON authors TOpk_authors' or something like that, so that I can rename theconstraint?Thanks in advance,Daniel Buus :)
View 9 Replies View RelatedHi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks
Hi there,
When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error message
Message
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Batch send failed
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number: 2627
Message: Violation of PRIMARY KEY constraint 'MSHREPL_1_PK'. Cannot insert duplicate key in object 'dbo.ITEMTRANSLATION'.
2006-06-24 12:41:59.216 Category:NULL
Source:
Number: 20253
What could possibly cause this error? And how can I possibly fix it?
Best regards,
JB
I have a copy of this database table and the first thing I noticed was that the Primary Key was pretty much useless and there were no sensible indexes. Every query hitting this table ended up table scanning.So I thought I would try dropping the existing Primary Key constraint and then creating a more natural key that would make data retrieval quicker (hopefully). I understand that creating a clustered index on this table is going to take a long time as ALL the data will need to be reorganised (I estimate this will take at least 1 hour). However, just dropping the existing Primary Key constraint is taking forever.I can see that the server is doing a lot of disk reading/ writing and the wait type in Activity Monitor is PAGEIOLATCH_EX.I would have thought that just dropping a primary key would not change the data in the table, just delete the associated index.
View 4 Replies View RelatedIn my SSIS package i am loading data from 1 source database to 2 targets database, both targets have same structure and datatype.
My package is working fine for 1 target database but for 2 nd database its giving error "Violation of PRIMARY KEY constraint" but where as primary key constraint is not violating .
Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
Please help!