curious problem in production

[Ollie Riches]

I am having a problem call a sql server stored procedure in a
test\production environment. I am getting an exception from sql server being
propagated back to the web service. The exception is a violation of primary
key constraint. The exception message is:

'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
in object 'candidateComponentEntries'

When I run the same code on the my development machine into the SAME
database it works perfectly fine. Yes the two different environments are
trying to insert into the same sql server database. It is being via ADO.Net
in an asp.net web service.

Background:
A BizTalk process is calling a web service to insert\process some data into
a sql server database and we have set it up to call my development machine
if it fails in calling the production environment. Then we used the sql
profiler to check the calls to the database and they produced the following:

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
.Net SqlClient Data Provider sa 0 53 2005-02-21 16:23:44.863

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0
0 0 0 53 2005-02-21 16:23:44.873

SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
16:23:44.873
RPC:Completed declare @P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332', @assessme
nt_ver_no = 1.000000000000000e+000, @component_id = N'01', @component_ver_no
= 0.000000000000000e+000, @candidate_uci = N'571330030125F',
@candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob =
'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000, @production_datetime
= 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
2005-02-21 16:23:44.883

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
.Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893

SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net SqlClient
Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0
0 0 0 59 2005-02-21 16:23:45.657

SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-21
16:23:45.657
RPC:Completed declare @P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657

SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider sa 0 0 0
0 0 59 2005-02-21 16:23:45.657


The first call to the stored procedure 'InsertCandidateQpEntry' and this
made from the production environment and the second call to this stored
procedure is made from my development machine and this succeeds. It appears
that the problem is not a code (my code) problem but maybe a problem with
the framework.

Production environment:
Windows 2003 Standard Edition
..Net Framework 1.1

Development environemnt:
XP Pro (2002) SP 1
..Net Framework 1.1

I even tried copying the development binaries to the production server and
it still fails.....

Any Ideas anyone?


Cheers in advance

Ollie Riches
http://www.phoneanalyser.net

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.

 

[JohnnyAppleseed]

A unique key constraint is a rule placed on a database table which basically
states that a column or combination of column values cannot be repeated. For
example, two employees cannot have the same social securoty number. Find out
what columns constitute the unique constraint called 'cce_uk1', and then
determine under what conditions the programming is attempting to insert the
a record with the same value twice.

 

[Alien2_51]

I have questions about your design most specifically about the part where you
put data into your development enviroment if you can't put it into your
production enviroment. I'm assuming you have something like merge replication
between these 2 environmets, if not how do you keep them in synch..? This
type of scnerio can get very ugly, typically you see alot of PK violations.
Why would you not wait in the BizTalk message box until you could put your
data into production...? please post DDL/DML

 

[Ollie Riches]

thank for the lesson on unique keys, but I suggest you read the question
again because this is NOT what I am asking about.

Ollie

 

[Ollie Riches]

Thanks for the answer. Just to clarify there is no connection between
development and production machines. The problem is that when I run the code
from production it fails to insert into the database (lets call it dbFoo)
but when I run the same code from my development machine against the SAME
database (yes the one I called dbFoo earlier in this sentence) IT SUCCEEDS.
So Basically the same code is calling the same stored procedure on the same
database, in one environment it fails and one it succeeds....

Weird?

Cheers

Ollie

 

[IPGrunt]

I am having a problem call a sql server stored procedure in a
test\production environment. I am getting an exception from sql server being
propagated back to the web service. The exception is a violation of primary
key constraint. The exception message is:

'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
in object 'candidateComponentEntries'

When I run the same code on the my development machine into the SAME
database it works perfectly fine. Yes the two different environments are
trying to insert into the same sql server database. It is being via ADO.Net
in an asp.net web service.

Background:
A BizTalk process is calling a web service to insert\process some data into
a sql server database and we have set it up to call my development machine
if it fails in calling the production environment. Then we used the sql
profiler to check the calls to the database and they produced the following:

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
.Net SqlClient Data Provider sa 0 53 2005-02-21 16:23:44.863

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0
0 0 0 53 2005-02-21 16:23:44.873

SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02- 21
16:23:44.873
RPC:Completed declare @P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332', @assessme
nt_ver_no = 1.000000000000000e+000, @component_id = N'01', @component_ver_no
= 0.000000000000000e+000, @candidate_uci = N'571330030125F',
@candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob =
'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000, @production_datetime
= 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
2005-02-21 16:23:44.883

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
.Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893

SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net SqlClient
Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0
0 0 0 59 2005-02-21 16:23:45.657

SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02- 21
16:23:45.657
RPC:Completed declare @P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657

SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider sa 0 0 0
0 0 59 2005-02-21 16:23:45.657


The first call to the stored procedure 'InsertCandidateQpEntry' and this
made from the production environment and the second call to this stored
procedure is made from my development machine and this succeeds. It appears
that the problem is not a code (my code) problem but maybe a problem with
the framework.

Production environment:
Windows 2003 Standard Edition
.Net Framework 1.1

Development environemnt:
XP Pro (2002) SP 1
.Net Framework 1.1

I even tried copying the development binaries to the production server and
it still fails.....

Any Ideas anyone?


Cheers in advance

Ollie Riches
http://www.phoneanalyser.net

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.

Ollie,

If I offered suggestions, I'd be shooting in the dark, of course.
Just some ideas to think about.

Are you using SQL or Windows authentication in the SQL server? Could
be an identity issue. What's the AD environment--domain based or
standalone?

I read your comment about it being a framework bug....how many times
have I thought that myself, only to find something dumb (in my own
stuff), later. Probably 99% certain it is NOT a framework bug.

BTW, what is the index: cce_uk1 ? Is it an autoincrement PK in
candiateComponentEntries, or is it some kind of FK relation to
another table? Is there some insert/update outside of a transaction
causing a race condition (possible if the server is on the same
physical processor as IIS/.NET.)?

Any triggers involved? (which can bite you from behind!)

I don't want answers...just hoping to jar something loose in your
brain.

Good luck.

-- ipgrunt

 

[Steve Kass]

I'm confused. What do you mean they are inserting into the
same database, if the two machines are not connected? How
do you know the data in the table they are inserting into is
identical on both machines? This error is saying something
about the data already present in the table.

If you know the data is the same, then maybe the collation
differs between the two machines.

Steve Kass
Drew University

 

[Ollie Riches]

see inline....

Ollie,

If I offered suggestions, I'd be shooting in the dark, of course.
Just some ideas to think about.

Are you using SQL or Windows authentication in the SQL server? Could
be an identity issue. What's the AD environment--domain based or
standalone?

sql authentication

I read your comment about it being a framework bug....how many times
have I thought that myself, only to find something dumb (in my own
stuff), later. Probably 99% certain it is NOT a framework bug.

I know, just thought I would grab at that straw as well

BTW, what is the index: cce_uk1 ? Is it an autoincrement PK in
candiateComponentEntries, or is it some kind of FK relation to
another table? Is there some insert/update outside of a transaction
causing a race condition (possible if the server is on the same
physical processor as IIS/.NET.)?

the point is I don't care about the keys, indexs or triggers it works when
calling from one environment why not from the other environment when it is
the SAME database.

Any triggers involved? (which can bite you from behind!)

Don't think so - it is not responsibility (they don't like developers
looking at their databases )

I don't want answers...just hoping to jar something loose in your
brain.

nothing came loose.....

 

[JohnnyAppleseed]

The error "'Violation of UNIQUE KEY constraint" has nothing to do with the
..NET framework or web services. It occurs when SQL Server prevents an insert
from placing a duplicate value in a column that has a unique key constraint.
Find out what is the unique key constraint and then fidn out why the same
value would be inserted twice.

 

[Ollie Riches]

I mean it is physically the same machine and the same database.....

so the database machine has an ip of 192.168.0.1
and the database is called FooDb

The production environment is trying to insert into a database (ip address
192.168.0.1) called FooDb - it FAILS
and the development environment is trying to insert into a database (ip
address 192.168.0.1) called FooDb - it SUCCEEDS

ie. the database is the same machine

hope that explains it more clearly...

Cheers

Ollie

 

[Ollie Riches]

thanks for the lesson again.....

Ollie

The error "'Violation of UNIQUE KEY constraint" has nothing to do with the
.NET framework or web services. It occurs when SQL Server prevents an
insert
from placing a duplicate value in a column that has a unique key
constraint.
Find out what is the unique key constraint and then fidn out why the same
value would be inserted twice.

 

[Steve Kass]

Your production environment *is* your development environment
and your development database *is* your production database?

Are you accessing the database from different client machines? If
so, it could be a collation issue, where string constants are interpreted
differently.

What do you mean when you say these procedure calls are "made"
from different machines - are you typing them in and running them
from Query Analyzer on separate machines connected to the same
database or what?

SK

 

[IPGrunt]

see inline....



sql authentication

OK, so the connection strings are identical?

You're not using DPAPI to encrypt passwords, are you? This gives
machine specific results, I believe.

I know, just thought I would grab at that straw as well

OK, you've been there, done that.

the point is I don't care about the keys, indexs or triggers it works when
calling from one environment why not from the other environment when it is
the SAME database.

Can you really aford to exclude that half of the system so soon?
You've heard of thinking outside the box? The trick usually involves
finding a bigger box.

Don't think so - it is not responsibility (they don't like developers
looking at their databases )

Territorial infighting never helped any large project. It's one of
the reasons that I enjoy consulting.

nothing came loose.....

Keep shaking!

ditto

-- ipgrunt

 

[Ollie Riches]

for the purposes of this test yes

the database is being accessed from different machines (one is production
environment windows 2003 and the other is my development machine windows XP
(2002 SP1)) both using the .Net framework version 1.1 and ADO.Net via a web
service.

hence the trace from sql profiler in the first message.

Cheers

Ollie

 

[Ollie Riches]

OK, so the connection strings are identical?

You're not using DPAPI to encrypt passwords, are you? This gives
machine specific results, I believe.

NO



OK, you've been there, done that.




Can you really aford to exclude that half of the system so soon?
You've heard of thinking outside the box? The trick usually involves
finding a bigger box.




Territorial infighting never helped any large project. It's one of
the reasons that I enjoy consulting.

Guess what I am....

Keep shaking!

getting a headache .....

 

[Steve Kass]

So this is my understanding. Make sure you can reproduce this
sequence.

1. There is a table candidateComponentEntries somewhere

2. Web service issues this statement to insert something (let us know
how you are certain this is precisely what the web service issued,
character for character)

declare @P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry
@candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output,
@MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003,
@session_month_code = N'3',
@session_year = 2005,
@candidate_no = 1.250000000000000e+002,
@centre_no = N'57133',
@business_stream_id = N'01',
@assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000,
@component_id = N'01',
@component_ver_no = 0.000000000000000e+000,
@candidate_uci = N'571330030125F',
@candidate_uci_type = N'UCI',
@candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM',
@qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'

3. You see this error:
'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
in object 'candidateComponentEntries'

4. There is no additional row in candidateComponentEntries.

5. You paste this identical statement into Query Analyzer on your
machine, which is connected to the same database, execute it,
and it succeeds.

6. From the production server, you can now see the new row in
the table candidateComponentEntries.

Can you post the CREATE TABLE statement of candidateComponentEntries,
and the definition of the unique constraint cce_uk1, and let us know
what values for the columns of cce_uk1 should be inserted by the
stored procedure? I assume you have looked at the table to find out
whether there is a row matching those values, but tell us if there is,
so we know whether you think the insert should fail or succeed in the
first place.

Do you really have a parameter called @candiateComponentEntries? If
not, and you typed instead of cut and pasted, can you post everything
by cutting and pasting so we know there are no typos?

SK

 

[Ollie Riches]

fundamentally what you describe below is what is happening.

The actual order of events is this:

1. BizTalk recieves a message, it calls the primary consumer to process this
message. This consumer is a web service in the production environment.

2. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:

@P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657

3. It FAILS complaining about the unique key constraint. The exception/error
is returned biztalk via the web service.

4. BizTalk calls it's secondary consumer to process the message because the
primary consumer failed. The secondary consumer is a web service on my
development environment.

5. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:

@P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657

6. It Succeeds

I am currently away from my machine and unable to examine the rows in the
table. I will tomorrow. The point being is that when I use only the
development environment it works perfectly fine - i.e BizTalk just calls the
web service in the development environment.

Cheers for the help.


Ollie