Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Services > curious problem in production

Reply
Thread Tools

curious problem in production

 
 
Ollie Riches
Guest
Posts: n/a
 
      02-21-2005
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.



 
Reply With Quote
 
 
 
 
JohnnyAppleseed
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>



 
Reply With Quote
 
 
 
 
Alien2_51
Guest
Posts: n/a
 
      02-21-2005
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.
>
>
>
>

 
Reply With Quote
 
Ollie Riches
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>>
>>

>
>



 
Reply With Quote
 
Ollie Riches
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>>
>>
>>
>>



 
Reply With Quote
 
IPGrunt
Guest
Posts: n/a
 
      02-21-2005
On 21 Feb 2005, "Ollie Riches" <(E-Mail Removed)>
postulated in news:(E-Mail Removed):

> 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

 
Reply With Quote
 
Steve Kass
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>
>
>>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.
>>>
>>>
>>>
>>>
>>>
>>>

>
>
>
>

 
Reply With Quote
 
Ollie Riches
Guest
Posts: n/a
 
      02-21-2005
see inline....

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



 
Reply With Quote
 
JohnnyAppleseed
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
> >>
> >>
> >>

> >
> >

>
>



 
Reply With Quote
 
Ollie Riches
Guest
Posts: n/a
 
      02-21-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>>news:(E-Mail Removed)...
>>
>>>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.
>>>>
>>>>
>>>>
>>>>
>>>>

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Curious problem when applying XSLT..... nick@codefresh.com Java 3 08-27-2005 01:56 AM
Curious problem applying XSTL in Java nick@codefresh.com XML 1 08-26-2005 10:20 AM
Curious Problem.. Rob Meade ASP .Net 17 04-27-2004 07:39 AM
Curious link button navigation problem Framework fan ASP .Net 4 03-08-2004 12:13 AM
another curious problem with mozilla nowhere man HTML 4 11-04-2003 07:00 PM



Advertisments