Posted by Erland Sommarskog on 10/13/07 16:56

(tatata9999@gmail.com) writes:
> I'm not sure I follow you this time.

And I am not sure that I am following you. You post some loose bits of
code that I don't really know what language it is. In the middle of at
you get an SQL error:

Violation of PRIMARY KEY constraint 'PK__myTBL__772828B3EFCA'. Cannot
insert duplicate key in object 'myUsers'

This error can have two reasons:

1) You insert multiple rows with INSERT SELECT and the SELECT produces
duplicate keys.
2) You attempt to insert a key value which is already there.

From what I make make out of your posts, you use INSERT VALUES to insert
a single row. This rules out the first possibility, and I conclude that
you attempt to insert a row with same key as an existing row. You say,
"Actually sql server has successfully captured one record. Don't know
why it tries to capture it again." But this is nonsense. SQL Server
does not go out in the wild to capture "capture rows", but sits there
waiting to be spoon-fed.

> the 'sudo' code is (why not spelt like this? :)
> set alphp = "A,B,C,D,E,$,!,
> %,^,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,&,*,(,)"
> set ran1 = Right(Rand(),3)
> set ran2 = Right(Rand(),3)
> set ran3 = ListGetAt(alphp,Right(Rand(),1))
>
> -- and initially the following line read
> set ran = #ran1#&#ran3#&#ran2#
>
> -- now the following line reads
> set ran = "#ran1#&#ran3#&#ran2#"
>
> I suspected that I forgot to "close"/quote the string var of ran. And
> that caused the intermittant error.

Why would the quotes have anything to do with a primary key violation?

If you go on generating random keys like this, you will sooner or later
generate a key that you have already generated. And if you don't have any
code to handle this, you will get a primary key violation when you try
to use the already existing key value.

Rather than using anything homegrown, why not use a GUID if you want a
random key? A GUID is a 128 bit-value and there function both in
Windows and SQL Server that generates GUIDs that are guaranteed to be
unique all over the planet. In SQL Server the data type for a GUID is
uniqueidentifier and you use newid() to generate a value.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]