«

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.

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.

"Ollie Riches" <> wrote in message
news:...
> 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.
>
>
>

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" wrote:

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

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


"JohnnyAppleseed" <> wrote in message
news:...
>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.
>
> "Ollie Riches" <> wrote in message
> news:...
>> 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.
>>
>>
>>
>
>

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
"Alien2_51" <> wrote in message
news:2AF7A354-54EC-4A94-814B-...
>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" wrote:
>
>> 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.
>>
>>
>>
>>

On 21 Feb 2005, "Ollie Riches" <>
postulated in news::

> 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

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 wrote:

>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
>"Alien2_51" <> wrote in message
>news:2AF7A354-54EC-4A94-814B-...
>
>
>>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" wrote:
>>
>>
>>
>>>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.
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>

see inline....

"IPGrunt" <> wrote in message
news:Xns960482D36A6EDswiss127army001wrenc@130.133. 1.4...
> On 21 Feb 2005, "Ollie Riches" <>
> postulated in news::
>
>> 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?

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

>
> Good luck.
>
> -- ipgrunt
>

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" <> wrote in message
news:...
> 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
>
>
> "JohnnyAppleseed" <> wrote in message
> news:...
> >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.
> >
> > "Ollie Riches" <> wrote in message
> > news:...
> >> 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.
> >>
> >>
> >>
> >
> >
>
>

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

"Steve Kass" <> wrote in message
news:...
> 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 wrote:
>
>>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
>>"Alien2_51" <> wrote in message
>>news:2AF7A354-54EC-4A94-814B-...
>>
>>>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" wrote:
>>>
>>>
>>>>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.
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
>>