Posted by tatata9999 on 10/13/07 18:05

On Oct 13, 11:56 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (tatata9...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Excellent, Erland, thank you.

[Back to original message]