when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar] (2)-ReceivedTime [datetime] (3)-Status [char]. Now here how to remove duplicate entry of same mobile number to MobileNumber field?
Hi, I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system Data is loaded into table from other sources because during data loads, the integrity constraints are disabled Merging data from disparate systems Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum ------------------------------------------------------- Bob Barker |2054| 52377 |235 Some road |9874 Bill Gates |5630| 69471 |014 Washington Rd. |3700 Bob Barker |2054| 97011 |235 Some road |9874 Bob Barker |2054| 40019 |235 Some road |9874 Steve Jobs |8501| 73051 |100 Infinity St. |4901 John Doe |7149| 86740 |7105 Bull Rd. |9282 Bill Gates |5630| 55970 |014 Washington Rd. |3700 Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874 Bill Gates |5630|69471/55970 |014 Washington Rd.|3700 Steve Jobs |8501|73051 |100 Infinity St. |4901 John Doe |7149|86740 |7105 Bull Rd. |9282 Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.
I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.
This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.
Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ? HTH, Jens K. Suessmeyer.
--- http://www.sqlserver2005.de ---
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
Hi All, I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`. Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`. I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file. TIA and you have a great day. David Nguyen.
I have a Transform Data Task that copies a lot of data from my source system. Unfortunately, I cannot use a DISTINCT in the SQL from the source system, due to a very poor ODBC driver! So, when I am creating my primary key, I am trying to do a lookup on the PK column before I insert the record to see if it exists. If it does, then I skip the row. The lookup references the target database of the task.
The problem I have is that the lookup doesn't find any duplicates loaded from the database. It allows them through and causes the database to throw a primary key error.
Has anyone experienced this, or think they know what I'm doing wrong?
I get the following error when I try to insert a stored procedure in an SQL-server.
Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'Login'.
My question if this is the real problem or the symtom of something else? I find it hard to believe that try to insert double key values. The table Login doesn´t contain any values.
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ? Thanks in advance ! stephen
I am putting my problem in an example as I Feel it would be clear.
Assume my table PEOPLE is having 4 columns with 6 rows, the SlNo being primary key. SlNo Name LastName birthdate 1 A B x -- 2 C B x |-- 1 pair (A, B, x) 3 D E y --|------------ 4 A E y | | 5 A B x __| |-- 2'nd pair (D, E, y) 6 D E y --------------- In this scenario, I need to find SlNo values having similar values in other columns. The o/p for above must be: 1 5 0 3 6 0 (0 needs to include in output for distinction in the sets)
(a)IS THIS POSSIBLE TO DO IN ONE SELECT STATEMET? and HOW? (b)If I create another temp table tempPEOPLE and select distinct row information of the 2'nd, 3'rd and 4'th columns from the PEOPLE table and then selecting SlNo's where the information match, I am able to get o/p 1 5 3 6 without 0...and I cannot makeout the distinct sets in this. HOW DO I FIND THE DISTINCTION IN SETS?
I have one table that stores log messages generated by a web service. I have a second table where I want to store just the distinct messages from the first table. This second table has two columns one for the message and the second for the checksum of the message. The checksum column is the primary key for the table.
My query for populating the second table looks like: INSERT INTO TransactionMessages ( message, messageHash ) SELECT DISTINCT message, CHECKSUM( message ) FROM Log WHERE logDate BETWEEN '2008-03-26 00:00:00' AND '2008-03-26 23:59:59' AND NOT EXISTS ( SELECT * FROM TransactionMessages WHERE messageHash = CHECKSUM( Log.message ) )
I run this query once per day to insert the new messages from the day before. It fails when a day has two messages that have the same checksum. In this case I would like to ignore the second message and let the query proceed. I tried creating an instead of insert trigger that only inserted unique primary keys. The trigger looks like:
IF( NOT EXISTS( SELECT TM.messageHash FROM TransactionMessages TM, inserted I WHERE TM.messageHash = I.messageHash ) ) BEGIN INSERT INTO TransactionMessages ( messageHash, message ) SELECT messageHash, message FROM inserted END
That didn't work. I think the issue is that all the rows get committed to the table at the end of the whole query. That means the trigger cannot match the duplicate primary key because the initial row has not been inserted yet.
I have 3 source for IS flow. One is flat file, one is DB table and one is output bad data. It might be a situation when I could have duplicate primary key since records come from 3 sources (flat file, db table, reject (output) table). Can any one give me suggestion how to handle duplicate primary key problem in this situation.
'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?
the point here that i have a small table with two fileds, ID (guid) as primerykey RAF(char) and the table is empty when i add a new row i recieve this exception, Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'. i found no way to solve the problem. thanks in advans
I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).
I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates.
I want to import a data file into a sql table. The table has a primary key but the data could have a duplicate value in the PK column (error in the source data). How can I "trap" for this type of error in SSIS?
Hi, in table aspnet_membership of ASPNETDB.mdb file, i saw a kind of duplicate field like "LoweredEmail" containing the same value as field "Email" (also in table users field "LoweredUsername" ..). I guess it's for containing email in lowercase, but why those fields? Thanks Tartuffe
I deliberately intend to add some duplicates to one of my tables. For eg
Job User IsAdmin
JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 JobID 235User ID 2 JobID 235User ID 9 JobID 235User ID 10 JobID 235User ID 1
I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:
Job User IsAdmin
JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 True JobID 235User ID 2 JobID 235User ID 9 True JobID 235User ID 10 JobID 235User ID 1 True
