Duplicate UID in PK column

Please excuse my noviceness, but how is it possible that I
can have duplicate records that are exactly the same in
every way when there is a PK field that is meant to be
unique?
Because this has happened, I've had to remove and re-index.
Can anyone please help this novice?That shouldn't happen, and I've never heard that it really happened. But it
is difficult to do anything or troubleshoot if you don't have a repro or
don't have the data anymore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Benjamin" <ben.jones@.trendwest.com.au> wrote in message
news:2c7301c3a8ad$61c25480$3101280a@.phx.gbl...
> Please excuse my noviceness, but how is it possible that I
> can have duplicate records that are exactly the same in
> every way when there is a PK field that is meant to be
> unique?
> Because this has happened, I've had to remove and re-index.
> Can anyone please help this novice?

duplicate rows - how to?

I have first_name field and i want to display duplicated rows only, i mean when the same exact name was there more than one time, like John and John...

Code Snippet

select first_name

from MyTable

groupby first_name

havingcount(*)>1

|||

Try:

select first_name

from dbo.t1

group by first_name

having count(*) > 1

AMB

|||

Using SQL Server 2005,

Code Snippet

;With CTE

as

(

Select First_Name,Row_Number() OVER (Partition By First_Name Order By First_Name) RowId From Table

)

Select Distinct First_Name from CTE Where RowId > 1

Duplicate key error

I have a table that I use an INSERT command to add a new record. The
primary key is an int field and I am calculating the highest # on table and
incrementing by 1 just before INSERT. On rare occasions, 2 people get the
same number and I get SQL error about duplicate. Is there a way to trap
this or prevent it and just assign a number 1 higher and try INSERT again
(note I cannot use Identity)? Thanks.
David"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
>I have a table that I use an INSERT command to add a new record. The
>primary key is an int field and I am calculating the highest # on table and
>incrementing by 1 just before INSERT. On rare occasions, 2 people get the
>same number and I get SQL error about duplicate. Is there a way to trap
>this or prevent it and just assign a number 1 higher and try INSERT again
>(note I cannot use Identity)? Thanks.
>
Which is exactly like saying "I am having trouble driving this nail with my
screwdriver (note I cannot use a hammer)."
IDENTITY is the right tool for the job. Everything else is an inferior
workaround. Now to get your inferior workaround to actually work, you must
wrap the select max(ID) and INSERT in a serializable transaction. Or
something like
BEGIN TRANSACTION
SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
insert T (ID,...) valueS (@.ID,...)
COMMIT TRANSACTION
BTW, you can perhaps use an IDENTITY column on a different table to generate
your ID's, eg
http://groups-beta.google.com/group...bcc24
68
David|||David,
How are you doing this?
AMB
"David C" wrote:

> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David
>
>|||Thank you. I agree, I always use Identity but have to wait for an old
conversion before I can change it to an identity. BTW, once I make it an
Identity field will it automatically set the SEED, or will I have to do that
with DBCC?
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
> Which is exactly like saying "I am having trouble driving this nail with
> my screwdriver (note I cannot use a hammer)."
> IDENTITY is the right tool for the job. Everything else is an inferior
> workaround. Now to get your inferior workaround to actually work, you
> must wrap the select max(ID) and INSERT in a serializable transaction. Or
> something like
> BEGIN TRANSACTION
> SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
> insert T (ID,...) valueS (@.ID,...)
> COMMIT TRANSACTION
> BTW, you can perhaps use an IDENTITY column on a different table to
> generate your ID's, eg
> http://groups-beta.google.com/group...bcc
2468
> David
>|||David C wrote:
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on
> table and incrementing by 1 just before INSERT. On rare occasions, 2
> people get the same number and I get SQL error about duplicate. Is
> there a way to trap this or prevent it and just assign a number 1
> higher and try INSERT again (note I cannot use Identity)? Thanks.
> David
You need to keep things locked in a transaction in order to prevent
users from stepping on one another. I generally prefer using another key
table that dishes out next key values if an identity can't be used.
In your case, you need to do something like this:
Begin Tran
Select @.NextID = MAX(ID) + 1 From TableA WITH (UPDLOCK, HOLDLOCK)
Insert TableA
Commit Tran
David Gugick
Imceda Software
www.imceda.com|||David,
You can also use a subquery in your INSERT statement, in this case you
don't need to use a transaction.
Instead of this:
insert MyTable values(ID, Col2, Col3, ..., ColN)
use this:
insert MyTable
select ID = IsNull((select max(ID+1) from MyTable), 1),
Col2,
Col3,
..,
ColN
Shervin
"David C" <dlchase@.lifetimeinc.com> wrote in message news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.ph
x.gbl>...
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David|||You'll need to recreate the table if you need to convert the existing values
to IDENTITY. You can't add the IDENTITY property to an existing column.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:eHQjdv6DFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Thank you. I agree, I always use Identity but have to wait for an old
> conversion before I can change it to an identity. BTW, once I make it an
> Identity field will it automatically set the SEED, or will I have to do
> that with DBCC?
> David
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
>|||> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
You can't prevent the assignment of duplicate values without a transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:4eaef17a.0502101720.4d2016f@.posting.google.com...
> David,
> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
> Instead of this:
> insert MyTable values(ID, Col2, Col3, ..., ColN)
>
> use this:
> insert MyTable
> select ID = IsNull((select max(ID+1) from MyTable), 1),
> Col2,
> Col3,
> ...,
> ColN
> Shervin
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl>...|||Dan,
You are absolutely right. I just tested my code and it failed. I
thought SQL Server would automatically start a transaction to protect
the execution of subquery, but I was wrong. Thanks for correcting me.
Regards,
Shervin
Dan Guzman wrote:
you
> You can't prevent the assignment of duplicate values without a
transaction.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:4eaef17a.0502101720.4d2016f@.posting.google.com...
you
The
table
get
to trap
INSERT again

duplicate entry in a primary key field

Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubid int Unchecked (primary key)
pub char(1) Unchecked
publ char(1) Unchecked
pubcode char(2) Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

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?

thanksAlex - you could probably do with a brush up on relational database design & theory.
http://r937.com/relational.html

1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.|||You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.|||there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?|||so with it is it okey if i will not make both a primary key?is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

in most many-to-many relationships, this would be an error|||so what's the best way i could do it?

thanks|||create a composite primary key on both columns|||You have a composite primary key - this is one primary key comproised of 2+ columns.

i.e.
CREATE TABLE MyTable
(
pubid int
, pub char(1)
, publ char(1)
, pubcode char(2)
, CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
)Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.

Duplicate Dimension Members

Hi everyone,

In AS 2000, some of the dimensions I was using had both the key & the name of the dimension as the same text field (dimTable.name). This grouped duplicate dimension members together. Browsing the dimension only showed unique members.

In SSAS, my understanding is you need a key attribute that links to the fact table, or another reference dimension. Since the dimension I was using had an intermediate key on another dimension table, I created a key in the dimension, and used a reference dimension to link indirectly to the fact table to another dimension.

Now I'm getting duplicate dimension members, since the keys are unique in the dimension table. Is there anyway I can consolidate these?

I'll try and diagram for a better explanation.

status dimension table --> dimension table --> fact table

status dimension table has key, name, foreignkey fields that links to a dimension. There can be many statuses for the dimension, based on the foreign key. There are duplicate statuses.

Any help is much appreciated!

...perhaps it's easier to understand if you post some sample data for your scenario... I get a little confused what's duplicate and what's not... Maybe you're talking about a n:m dimension...|||

It seems to be happening not just with the dimension with 2 reference tables, but a standard 1-M dimension-fact relationship.

1 example.

DIM table.

STAT_ID - STAT_NAME

1 Andrew
2 Thomas
3 Thomas
4 Andrew
5 Andrew

Fact Table

STAT_ID
1
1
3
4

AS 2000 Dimension

Key - STAT_NAME
Name - STAT_NAME

Relationship in Cube - STAT_ID - STAT_ID

The result is 1 name in dimensino but keys are related.

AS 2005 Dimension
Key - Stat_ID
Name - Stat Name

A new attribute is created for Stat_ID
This attribute is used in cube Dimensions tab.
The tables are linked in DSV to STAT_ID

The result is duplicate names, due to different keys.

One suggestion is to create a Named query & join on the names in the DSV. I don't want to do that for every dimension we have KEY & Name with the same field in AS 2000.

If I change the Key in the dimension back to STAT_NAME, I can no longer link the granularity settings in the cube.

|||

Andrew,

what's the reason for the key in the dimension table not being unique? Perhaps you should setup some ETL to make this "clean"...

|||

I was thinking about doing that, however I didn't want to change the 2000 data model. Can I do that in the DSV, by just creating a derived query on the name column?

The reason they're not unique is because the data isn't totally denormalized. The actual table looks more like...

key -- status name -- child name

where status name is not unique, but the child is.

So do you think we should split group name off into it's own table, or is there a way to set a property on the dimension so that it will ignore duplicates? It seemed to work in AS 2000.

thanks for all your help,

Andrew

Dumb Data Storage Question

Hello,

So, here's my dumb question; if I wanted to store some *.gif images in some database (SQL2K possibly 2K5) field and wanted to pull the information from that to display on the web form, am I actually storing the image in the database or am I storing the location of the image in the database?

I ask this because I was under the impression that the location to the image file is what was being stored but another person was saying that it was the actual image. I guess I'm confused...

Thanks in advance...

You could do either. If you are not sure check the datatypes of the columns in the table. If its a varchar(50) obviously its only storing the path. You can also do a select from the table and see the values in the column. If you see some hexadecimal values its an image. if its a path you can see it right away.|||

So if I wanted to store an the actual image in a DB table, I'd store it as a hexidecimal date type, correct? That makes sense...

So in order to get that image into the table in the first place I could just do an upload from a web UI, correct?

Thanks!

|||Kind of. Please check documentation regarding Image data types. Also do some googling and understand the consequences of storing images in databases. Databases are better storing data than images.|||

Cool. Thanks.

I agree with you, for whatever that's worth. I would have just put a location path in the db to pull the images in but thought if you can just store the whole image, why not do it that way? But I think the path is the way to go. especially if you've standardized the path of the images to a specific location.

Thanks again!

DTSX package data transfer error

I will try to explain things the best I can.

When the data is transferred from source to destination (replace not append), the data in one field in one table is incorrect. Both source and destination tables have the same number of rows (8493). The ProductID field data range at the source is from 58958 to 73008. When the table is copied the ProductID field data runs from 1 to 8493.

What would cause this skewing of data?

This happens on brand new dtsx packages and this only happens in one field out of 5 different tables.

I am baffled. Any help is appreciated.

Thanks.

Andrew

Sounds like ProductID is an identity field.

In the OLE DB destination, ensure that you are using a data access mode of "Table or view - fast load" and then make sure the check box is selected for "Keep identity."

See if that helps.|||

Thanks for the quick response Phil.

Where exactly do I look for this setting in Visual Studio?

I must be overlooking it.

Thanks.

Andrew

|||It's in the OLE DB Destination component. Double click on it.|||Did you develop the package, or use the Import/Export wizard?|||I devolped it.|||

FordyH500HP wrote:

I devolped it.

Then what destination are you using? Use the OLE DB Destination if you're not using it already.|||

I'm using the Transfer SQL Server Objects Task from the toolbox.

Will that work for what you are talking about?

|||

FordyH500HP wrote:

I'm using the Transfer SQL Server Objects Task from the toolbar.

Will that work for what you are talking about?

Ahh... What values for the options under "Table Options" do you have?|||

Sorry if I should have stated that sooner.

I'm just above Newbie status in Visual Studio.

CopyIndexes - True

CopyTriggers - False

CopyFullTextIndexes - False

CopyPrimaryKeys - False

CopyForeignKeys - False

GenerateScriptsInUnicode - False

|||What happens if you set CopyPrimaryKeys and CopyForeignKeys to True?|||Same result. It renumbers the ProductID field starting with 1.|||

FordyH500HP wrote:

Same result. It renumbers the ProductID field starting with 1.

Yep, doing a quick search shows that the Transfer SQL Server Objects task does not support identity columns. Will future versions?

Let's find out:
[Microsoft follow-up]

You can do this on your own though, by using an execute sql task in the control flow to truncate the destination tables and then as many data flows as you have tables. Inside the data flows, you can use an OLE DB source and hook it up to an OLE DB destination, and use the options I suggested above.|||

Ok. I think I understand.

So I want to check Keep Identity then in the data flow destination?

Also, can I put multiple Source/Destinations in one data flow?

|||Yep

DTS: Converting datatype in the source before import

Hi, In my source (text file connection) there is a date field but the date
is stored yyyymmdd (20080226)
I need to convert this into a real date 02/26/2008 and then import it into
the sql server database into a datetime field.
I'm a newbie with DTS so very detailed insturctions would be greatly
appreciated. When I just tried having it import into SQL Server I got an
error obviously since the date in the source data is not in a format sql
server will understandI'm not going to get detailed but I'll give you one way to do this. You'll
have to do some research on your own.
Import into a varchar column then use convert to transform and push into
your destination datetime column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi, In my source (text file connection) there is a date field but the date
> is stored yyyymmdd (20080226)
> I need to convert this into a real date 02/26/2008 and then import it into
> the sql server database into a datetime field.
> I'm a newbie with DTS so very detailed insturctions would be greatly
> appreciated. When I just tried having it import into SQL Server I got an
> error obviously since the date in the source data is not in a format sql
> server will understand
>
>|||i forgot to mention use convert function when pushing varchar into datetime
column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Knowledgy" <atlanta business intelligence consultants> wrote in message
news:XaednbkftahIS1nanZ2dnUVZ_gWdnZ2d@.comcast.com...
> I'm not going to get detailed but I'll give you one way to do this.
> You'll have to do some research on your own.
> Import into a varchar column then use convert to transform and push into
> your destination datetime column
> --
> Sincerely,
> John K
> Knowledgy Consulting
> http://knowledgy.org/
> Atlanta's Business Intelligence and Data Warehouse Experts
>
> "Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
> news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi, In my source (text file connection) there is a date field but the
>> date is stored yyyymmdd (20080226)
>> I need to convert this into a real date 02/26/2008 and then import it
>> into the sql server database into a datetime field.
>> I'm a newbie with DTS so very detailed insturctions would be greatly
>> appreciated. When I just tried having it import into SQL Server I got an
>> error obviously since the date in the source data is not in a format sql
>> server will understand
>>
>

DTS Truncating field from 8000 to 255

hi,
I am using DTS to export to a text file and i have a field that is a
VARCHAR(8000) field. It is being truncated to 255. When I look at the
destination tab in DTS, it says that the size is 8000. Any ideas?
please advise.
rafaelhow do you know it is being truncated?. Are you doing a query using Query
Analyzer?, if so, then you have to change the value of the option Tools -
Options... - Results - "Maximum characters per column", the default is 256
and the max value is 8k or 8192.
AMB
"Rafael Chemtob" wrote:

> hi,
> I am using DTS to export to a text file and i have a field that is a
> VARCHAR(8000) field. It is being truncated to 255. When I look at the
> destination tab in DTS, it says that the size is 8000. Any ideas?
> please advise.
> rafael
>
>|||Hi,
Looks like you got problems while retrieval. If it is a problem with
retrieval then use the below commands:-
SET TEXTSIZE { number }
select @.@.textsize
See books online for more details.
Thanks
Hari
SQL Server MVP
"Rafael Chemtob" wrote:

> hi,
> I am using DTS to export to a text file and i have a field that is a
> VARCHAR(8000) field. It is being truncated to 255. When I look at the
> destination tab in DTS, it says that the size is 8000. Any ideas?
> please advise.
> rafael
>
>

DTS to Excel Export - Changing Format

Hi,

I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.

The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.

But when I open this file that decimal field is displayed as a string column and its left aligned.

Is there any way to fix this problem?

Thanks,I've never seen this happen. You aren't wrapping you values in quotes are you when you export them.|||No, I am not wrapping values in quotes.

DTS to add a field on imported data

I am in the process of importing data that is in a text format to the
sql server, I want to add couple of fields and insert general data in
the fields added, this data is going to be similar to all the records
imported. Your help in this regard will be greatly appreciated.Hi

If you have created a DTS package to do this import you could add and
Execute SQL step to do an update statement. Alternatively, if the value is
to be the same for each record, then a default constraint on the column will
populate the initial values.

John

"John R" <admin@.wirelesscybercafe.com> wrote in message
news:a486f7fa.0409170810.3bdc4def@.posting.google.c om...
> I am in the process of importing data that is in a text format to the
> sql server, I want to add couple of fields and insert general data in
> the fields added, this data is going to be similar to all the records
> imported. Your help in this regard will be greatly appreciated.