Would special character cause insertion problem?

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.

Re: Would special character cause insertion problem?

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




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.
>

Re: Would special character cause insertion problem?

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'.

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

On Oct 12, 11:01 am, "Jack Vamvas" 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
>
> 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 -

Re: Would special character cause insertion problem?

(tatata9999@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'.

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Would special character cause insertion problem?

On Oct 12, 4:16 pm, Erland Sommarskog 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'.

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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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

Re: Would special character cause insertion problem?

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'.

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

> On Oct 12, 11:01 am, "Jack Vamvas" 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
>
> > 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 -

Re: Would special character cause insertion problem?

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Would special character cause insertion problem?

On Oct 13, 11:56 am, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Excellent, Erland, thank you.