Would special character cause insertion problem?

[tatata9999]

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.

[Jack Vamvas]

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


<ta********@gma il.comwrote in message
news:11******** *************@v 29g2000prd.goog legroups.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]

Hi Jack,

Very simple sql dml code (all the columns are of varchar of various
length):
insert into myTBL (col1,col2,col3 )
values ('#ranVar#','#F ORM.element1#', '#FORM.element2 #')

The err msg is:
Violation of PRIMARY KEY constraint 'PK__myTBL__772 828B3EFCA'. Cannot
insert duplicate key in object 'myUsers'.<P><P SQL = "insert into
myTBL (col1,col2,col3 ) values
('342*783','s** ************@st udent.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" <DEL_TO_RE...@d el.comwrote:

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...@gma il.comwrote in message

news:11******** *************@v 29g2000prd.goog legroups.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 -

[Erland Sommarskog]

(ta********@gma il.com) writes:

Very simple sql dml code (all the columns are of varchar of various
length):
insert into myTBL (col1,col2,col3 )
values ('#ranVar#','#F ORM.element1#', '#FORM.element2 #')

The err msg is:
Violation of PRIMARY KEY constraint 'PK__myTBL__772 828B3EFCA'. Cannot
insert duplicate key in object 'myUsers'.<P><P SQL = "insert into
myTBL (col1,col2,col3 ) values
('342*783','s** ************@st udent.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tatata9999]

On Oct 12, 4:16 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(tatata9...@gma il.com) writes:

Very simple sql dml code (all the columns are of varchar of various
length):
insert into myTBL (col1,col2,col3 )
values ('#ranVar#','#F ORM.element1#', '#FORM.element2 #')

The err msg is:
Violation of PRIMARY KEY constraint 'PK__myTBL__772 828B3EFCA'. Cannot
insert duplicate key in object 'myUsers'.<P><P SQL = "insert into
myTBL (col1,col2,col3 ) values
('342*783','sig nmeeup728...@st udent.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...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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

[tatata9999]

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__772 828B3EFCA'. Cannot
insert duplicate key in object 'myTBL'.<P><P>
SQL = "insert into myTBL (col1,col2,col3 ) values
('342*783','sig nmeeup728...@st udent.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" <DEL_TO_RE...@d el.comwrote:

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...@gma il.comwrote in message

news:11******** *************@v 29g2000prd.goog legroups.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 -

[Erland Sommarskog]

(ta********@gma il.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__772 828B3EFCA'. 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
uniqueidentifie r and you use newid() to generate a value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tatata9999]

On Oct 13, 11:56 am, Erland Sommarskog <esq...@sommars kog.sewrote:

(tatata9...@gma il.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__772 828B3EFCA'. 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
uniqueidentifie r and you use newid() to generate a value.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Excellent, Erland, thank you.