|
|
You're in the
sql server (alternate)
group:Would special character cause insertion problem?
sql server (alternate):
SQL env: sql server 2000 Target column of insertion: varchar(15) Case, a var is made up of a character of the following characters (random selection): 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,&,*,(,) and a few numbers (random selection), and then var = ran1&ran2 I'm experiencing intermit sql error Is it because I did not include the string with quotes like var = "ran1 & ran2" or the first random seletion may include special character and that may cause insertion error randomly? Probably the former is more likely. And I've added quotes for the var (programming language shouldn't really matter, int type usually without quotes while strings need quotes). What's your thought? Thanks.
Hi Jack,
Very simple sql dml code (all the columns are of varchar of various length): insert into myTBL (col1,col2,col3) values ('#ranVar#','#FORM.element1#','#FORM.element2#') The err msg is: Violation of PRIMARY KEY constraint 'PK__myTBL__772828B3EFCA'. Cannot insert duplicate key in object 'myUsers'.<P><P> SQL = "insert into myTBL (col1,col2,col3) values ('342*783','signmeeup728392@student.org','s')" " Actually sql server has successfully captured one record. Don't know why it tries to capture it again. It would be nice to have many simultanous users to use/test it... If you don't mind, http://www.mytata.net/, Many thanks. Don [quoted text, click to view] On Oct 12, 11:01 am, "Jack Vamvas" <DEL_TO_RE...@del.com> wrote:
> Could you post your stored procedure or code? > Are you able to get an output of the sql string that will be used, the one > that throws the error. > > -- > > Jack Vamvas > ___________________________________ > Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL > > <tatata9...@gmail.com> wrote in message > > news:1192198893.793558.61740@v29g2000prd.googlegroups.com... > > > > > Hi, > > > SQL env: sql server 2000 > > Target column of insertion: varchar(15) > > > Case, a var is made up of a character of the following characters > > (random selection): > > 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,&,*,(,) > > and a few numbers (random selection), and then > > > var = ran1&ran2 > > > I'm experiencing intermit sql error > > Is it because I did not include the string with quotes like > > var = "ran1 & ran2" > > > or the first random seletion may include special character and that > > may cause insertion error randomly? > > > Probably the former is more likely. And I've added quotes for the > > var (programming language shouldn't really matter, int type usually > > without quotes while strings need quotes). > > > What's your thought? > > > Thanks.- Hide quoted text - > > - Show quoted text -
[quoted text, click to view]
On Oct 12, 4:16 pm, Erland Sommarskog <esq...@sommarskog.se> wrote: > (tatata9...@gmail.com) writes: > > Very simple sql dml code (all the columns are of varchar of various > > length): > > insert into myTBL (col1,col2,col3) > > values ('#ranVar#','#FORM.element1#','#FORM.element2#') > > > The err msg is: > > Violation of PRIMARY KEY constraint 'PK__myTBL__772828B3EFCA'. Cannot > > insert duplicate key in object 'myUsers'.<P><P> SQL = "insert into > > myTBL (col1,col2,col3) values > > ('342*783','signmeeup728...@student.org','s')" " > > > Actually sql server has successfully captured one record. Don't know > > why it tries to capture it again. It would be nice to have many > > simultanous users to use/test it... If you don't mind, > >http://www.mytata.net/, > > If I understood your initial post correctly, you generated the value > by some random process. Isn't it as simple that you are generating the > same value twice? > > -- > 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- Hide quoted text - > > - Show quoted text - Erland, I'm not sure I follow you this time. Here's some additional info. 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. And I always appreciate your help. Don
Sorry for security reason, I have to remove the previous post and
repost it with revision. The err msg is: Violation of PRIMARY KEY constraint 'PK__myTBL__772828B3EFCA'. Cannot insert duplicate key in object 'myTBL'.<P><P> SQL = "insert into myTBL (col1,col2,col3) values ('342*783','signmeeup728...@student.org','s')" " Actually sql server has successfully captured one record. Don't know why it tries to capture it again. It would be nice to have many simultanous users to use/test it... If you don't mind, http://www.mytata.net/, Many thanks. Don [quoted text, click to view] > On Oct 12, 11:01 am, "Jack Vamvas" <DEL_TO_RE...@del.com> wrote: > > > > > Could you post your stored procedure or code? > > Are you able to get an output of the sql string that will be used, the one > > that throws the error. > > > -- > > > Jack Vamvas > > ___________________________________ > > Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL > > > <tatata9...@gmail.com> wrote in message > > >news:1192198893.793558.61740@v29g2000prd.googlegroups.com... > > > > Hi, > > > > SQL env: sql server 2000 > > > Target column of insertion: varchar(15) > > > > Case, a var is made up of a character of the following characters > > > (random selection): > > > 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,&,*,(,) > > > and a few numbers (random selection), and then > > > > var = ran1&ran2 > > > > I'm experiencing intermit sql error > > > Is it because I did not include the string with quotes like > > > var = "ran1 & ran2" > > > > or the first random seletion may include special character and that > > > may cause insertion error randomly? > > > > Probably the former is more likely. And I've added quotes for the > > > var (programming language shouldn't really matter, int type usually > > > without quotes while strings need quotes). > > > > What's your thought? > > > > Thanks.- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
Could you post your stored procedure or code?
Are you able to get an output of the sql string that will be used, the one that throws the error. -- Jack Vamvas ___________________________________ Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL [quoted text, click to view] <tatata9999@gmail.com> wrote in message news:1192198893.793558.61740@v29g2000prd.googlegroups.com... > Hi, > > SQL env: sql server 2000 > Target column of insertion: varchar(15) > > Case, a var is made up of a character of the following characters > (random selection): > 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,&,*,(,) > and a few numbers (random selection), and then > > var = ran1&ran2 > > I'm experiencing intermit sql error > Is it because I did not include the string with quotes like > var = "ran1 & ran2" > > or the first random seletion may include special character and that > may cause insertion error randomly? > > Probably the former is more likely. And I've added quotes for the > var (programming language shouldn't really matter, int type usually > without quotes while strings need quotes). > > What's your thought? > > Thanks. >
(tatata9999@gmail.com) writes:
[quoted text, click to view] > Very simple sql dml code (all the columns are of varchar of various > length): > insert into myTBL (col1,col2,col3) > values ('#ranVar#','#FORM.element1#','#FORM.element2#') > > The err msg is: > Violation of PRIMARY KEY constraint 'PK__myTBL__772828B3EFCA'. Cannot > insert duplicate key in object 'myUsers'.<P><P> SQL = "insert into > myTBL (col1,col2,col3) values > ('342*783','signmeeup728392@student.org','s')" " > > Actually sql server has successfully captured one record. Don't know > why it tries to capture it again. It would be nice to have many > simultanous users to use/test it... If you don't mind, > http://www.mytata.net/, If I understood your initial post correctly, you generated the value by some random process. Isn't it as simple that you are generating the same value twice? -- 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
[quoted text, click to view]
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.
(tatata9999@gmail.com) writes:
[quoted text, click to view] > 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. [quoted text, click to view] > 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
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |