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" <ollie.riches@.phoneanalser.net> wrote in message
news:e1SjhdDGFHA.1260@.TK2MSFTNGP12.phx.gbl...
> 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" <someone@.microsoft.com> wrote in message
news:uYmSCoDGFHA.3728@.TK2MSFTNGP14.phx.gbl...
>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" <ollie.riches@.phoneanalser.net> wrote in message
> news:e1SjhdDGFHA.1260@.TK2MSFTNGP12.phx.gbl...
>> 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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>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" <ollie.riches@.phoneanalser.net>
postulated in news:e1SjhdDGFHA.1260@.TK2MSFTNGP12.phx.gbl:
> 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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
>news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>
>>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.
>>
>>
>>
>
>|||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" <ollie.riches@.phoneanalyser.net> wrote in message
news:eG3iKvEGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> 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" <someone@.microsoft.com> wrote in message
> news:uYmSCoDGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> >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" <ollie.riches@.phoneanalser.net> wrote in message
> > news:e1SjhdDGFHA.1260@.TK2MSFTNGP12.phx.gbl...
> >> 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" <skass@.drew.edu> wrote in message
news:421A3D93.8060100@.drew.edu...
> 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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
>>news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>>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.
>>
>>
>>
>>|||thanks for the lesson again....
Ollie
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:OPSNiBFGFHA.1260@.TK2MSFTNGP12.phx.gbl...
> 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" <ollie.riches@.phoneanalyser.net> wrote in message
> news:eG3iKvEGFHA.1740@.TK2MSFTNGP09.phx.gbl...
>> 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" <someone@.microsoft.com> wrote in message
>> news:uYmSCoDGFHA.3728@.TK2MSFTNGP14.phx.gbl...
>> >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" <ollie.riches@.phoneanalser.net> wrote in message
>> > news:e1SjhdDGFHA.1260@.TK2MSFTNGP12.phx.gbl...
>> >> 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.
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Your production environment *is* your development environment
and your development database *is* your production database?
Are you accessing the database from different client machines? If
so, it could be a collation issue, where string constants are interpreted
differently.
What do you mean when you say these procedure calls are "made"
from different machines - are you typing them in and running them
from Query Analyzer on separate machines connected to the same
database or what?
SK
Ollie Riches wrote:
>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" <skass@.drew.edu> wrote in message
>news:421A3D93.8060100@.drew.edu...
>
>>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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
>>news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>>
>>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.
>>
>>
>>
>>
>>
>
>|||for the purposes of this test yes
the database is being accessed from different machines (one is production
environment windows 2003 and the other is my development machine windows XP
(2002 SP1)) both using the .Net framework version 1.1 and ADO.Net via a web
service.
hence the trace from sql profiler in the first message.
Cheers
Ollie
"Steve Kass" <skass@.drew.edu> wrote in message
news:421A40EE.8040507@.drew.edu...
> Your production environment *is* your development environment
> and your development database *is* your production database?
> Are you accessing the database from different client machines? If
> so, it could be a collation issue, where string constants are interpreted
> differently.
> What do you mean when you say these procedure calls are "made"
> from different machines - are you typing them in and running them
> from Query Analyzer on separate machines connected to the same
> database or what?
> SK
> Ollie Riches wrote:
>>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" <skass@.drew.edu> wrote in message
>>news:421A3D93.8060100@.drew.edu...
>>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" <dan.billow.remove@.monacocoach.removeme.com> wrote in
>>message news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>>
>>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.
>>
>>
>>
>>
>>|||So this is my understanding. Make sure you can reproduce this
sequence.
1. There is a table candidateComponentEntries somewhere
2. Web service issues this statement to insert something (let us know
how you are certain this is precisely what the web service issued,
character for character)
declare @.P1 bigint
set @.P1=858
declare @.P2 bigint
set @.P2=776
declare @.P3 varchar(1)
set @.P3='Y'
exec InsertCandidateQpEntry
@.candiateComponentEntries_id = @.P1 output,
@.candidateQpMarkSets_id = @.P2 output,
@.MarksAlreadyExists = @.P3 output,
@.eps_session_sid = 2.085000000000000e+003,
@.session_month_code = N'3',
@.session_year = 2005,
@.candidate_no = 1.250000000000000e+002,
@.centre_no = N'57133',
@.business_stream_id = N'01',
@.assessment_code = N'2332',
@.assessment_ver_no = 1.000000000000000e+000,
@.component_id = N'01',
@.component_ver_no = 0.000000000000000e+000,
@.candidate_uci = N'571330030125F',
@.candidate_uci_type = N'UCI',
@.candidate_gender = N'M',
@.candidate_dob = 'May 30 1989 12:00AM',
@.qp_id = 1.000000000000000e+000,
@.production_datetime = 'Feb 21 2005 4:21PM'
3. You see this error:
'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
in object 'candidateComponentEntries'
4. There is no additional row in candidateComponentEntries.
5. You paste this identical statement into Query Analyzer on your
machine, which is connected to the same database, execute it,
and it succeeds.
6. From the production server, you can now see the new row in
the table candidateComponentEntries.
Can you post the CREATE TABLE statement of candidateComponentEntries,
and the definition of the unique constraint cce_uk1, and let us know
what values for the columns of cce_uk1 should be inserted by the
stored procedure? I assume you have looked at the table to find out
whether there is a row matching those values, but tell us if there is,
so we know whether you think the insert should fail or succeed in the
first place.
Do you really have a parameter called @.candiateComponentEntries? If
not, and you typed instead of cut and pasted, can you post everything
by cutting and pasting so we know there are no typos?
SK
Ollie Riches wrote:
>for the purposes of this test yes
>the database is being accessed from different machines (one is production
>environment windows 2003 and the other is my development machine windows XP
>(2002 SP1)) both using the .Net framework version 1.1 and ADO.Net via a web
>service.
>hence the trace from sql profiler in the first message.
>Cheers
>Ollie
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:421A40EE.8040507@.drew.edu...
>
>>Your production environment *is* your development environment
>>and your development database *is* your production database?
>>Are you accessing the database from different client machines? If
>>so, it could be a collation issue, where string constants are interpreted
>>differently.
>>What do you mean when you say these procedure calls are "made"
>>from different machines - are you typing them in and running them
>>from Query Analyzer on separate machines connected to the same
>>database or what?
>>SK
>>Ollie Riches wrote:
>>
>>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" <skass@.drew.edu> wrote in message
>>news:421A3D93.8060100@.drew.edu...
>>
>>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" <dan.billow.remove@.monacocoach.removeme.com> wrote in
>>message news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>>
>>
>>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.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>|||fundamentally what you describe below is what is happening.
The actual order of events is this:
1. BizTalk recieves a message, it calls the primary consumer to process this
message. This consumer is a web service in the production environment.
2. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:
@.P1 bigint
set @.P1=858
declare @.P2 bigint
set @.P2=776
declare @.P3 varchar(1)
set @.P3='Y'
exec InsertCandidateQpEntry @.candiateComponentEntries_id = @.P1 output,
@.candidateQpMarkSets_id = @.P2 output, @.MarksAlreadyExists = @.P3 output,
@.eps_session_sid = 2.085000000000000e+003, @.session_month_code = N'3',
@.session_year = 2005, @.candidate_no = 1.250000000000000e+002, @.centre_no =N'57133', @.business_stream_id = N'01', @.assessment_code = N'2332',
@.assessment_ver_no = 1.000000000000000e+000, @.component_id = N'01',
@.component_ver_no = 0.000000000000000e+000, @.candidate_uci =N'571330030125F', @.candidate_uci_type = N'UCI', @.candidate_gender = N'M',
@.candidate_dob = 'May 30 1989 12:00AM', @.qp_id = 1.000000000000000e+000,
@.production_datetime = 'Feb 21 2005 4:21PM'
select @.P1, @.P2, @.P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657
3. It FAILS complaining about the unique key constraint. The exception/error
is returned biztalk via the web service.
4. BizTalk calls it's secondary consumer to process the message because the
primary consumer failed. The secondary consumer is a web service on my
development environment.
5. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:
@.P1 bigint
set @.P1=858
declare @.P2 bigint
set @.P2=776
declare @.P3 varchar(1)
set @.P3='Y'
exec InsertCandidateQpEntry @.candiateComponentEntries_id = @.P1 output,
@.candidateQpMarkSets_id = @.P2 output, @.MarksAlreadyExists = @.P3 output,
@.eps_session_sid = 2.085000000000000e+003, @.session_month_code = N'3',
@.session_year = 2005, @.candidate_no = 1.250000000000000e+002, @.centre_no =N'57133', @.business_stream_id = N'01', @.assessment_code = N'2332',
@.assessment_ver_no = 1.000000000000000e+000, @.component_id = N'01',
@.component_ver_no = 0.000000000000000e+000, @.candidate_uci =N'571330030125F', @.candidate_uci_type = N'UCI', @.candidate_gender = N'M',
@.candidate_dob = 'May 30 1989 12:00AM', @.qp_id = 1.000000000000000e+000,
@.production_datetime = 'Feb 21 2005 4:21PM'
select @.P1, @.P2, @.P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657
6. It Succeeds
I am currently away from my machine and unable to examine the rows in the
table. I will tomorrow. The point being is that when I use only the
development environment it works perfectly fine - i.e BizTalk just calls the
web service in the development environment.
Cheers for the help.
Ollie
"Steve Kass" <skass@.drew.edu> wrote in message
news:421A46C9.9000109@.drew.edu...
> So this is my understanding. Make sure you can reproduce this
> sequence.
> 1. There is a table candidateComponentEntries somewhere
> 2. Web service issues this statement to insert something (let us know
> how you are certain this is precisely what the web service issued,
> character for character)
> declare @.P1 bigint
> set @.P1=858
> declare @.P2 bigint
> set @.P2=776
> declare @.P3 varchar(1)
> set @.P3='Y'
> exec InsertCandidateQpEntry
> @.candiateComponentEntries_id = @.P1 output,
> @.candidateQpMarkSets_id = @.P2 output,
> @.MarksAlreadyExists = @.P3 output,
> @.eps_session_sid = 2.085000000000000e+003,
> @.session_month_code = N'3',
> @.session_year = 2005,
> @.candidate_no = 1.250000000000000e+002,
> @.centre_no = N'57133',
> @.business_stream_id = N'01',
> @.assessment_code = N'2332',
> @.assessment_ver_no = 1.000000000000000e+000,
> @.component_id = N'01',
> @.component_ver_no = 0.000000000000000e+000,
> @.candidate_uci = N'571330030125F',
> @.candidate_uci_type = N'UCI',
> @.candidate_gender = N'M',
> @.candidate_dob = 'May 30 1989 12:00AM',
> @.qp_id = 1.000000000000000e+000,
> @.production_datetime = 'Feb 21 2005 4:21PM'
> 3. You see this error:
> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
> in object 'candidateComponentEntries'
> 4. There is no additional row in candidateComponentEntries.
> 5. You paste this identical statement into Query Analyzer on your
> machine, which is connected to the same database, execute it,
> and it succeeds.
> 6. From the production server, you can now see the new row in
> the table candidateComponentEntries.
> Can you post the CREATE TABLE statement of candidateComponentEntries,
> and the definition of the unique constraint cce_uk1, and let us know
> what values for the columns of cce_uk1 should be inserted by the
> stored procedure? I assume you have looked at the table to find out
> whether there is a row matching those values, but tell us if there is,
> so we know whether you think the insert should fail or succeed in the
> first place.
> Do you really have a parameter called @.candiateComponentEntries? If
> not, and you typed instead of cut and pasted, can you post everything
> by cutting and pasting so we know there are no typos?
> SK
>
>
> Ollie Riches wrote:
>>for the purposes of this test yes
>>the database is being accessed from different machines (one is production
>>environment windows 2003 and the other is my development machine windows
>>XP (2002 SP1)) both using the .Net framework version 1.1 and ADO.Net via a
>>web service.
>>hence the trace from sql profiler in the first message.
>>Cheers
>>Ollie
>>
>>
>>"Steve Kass" <skass@.drew.edu> wrote in message
>>news:421A40EE.8040507@.drew.edu...
>>Your production environment *is* your development environment
>>and your development database *is* your production database?
>>Are you accessing the database from different client machines? If
>>so, it could be a collation issue, where string constants are interpreted
>>differently.
>>What do you mean when you say these procedure calls are "made"
>>from different machines - are you typing them in and running them
>>from Query Analyzer on separate machines connected to the same
>>database or what?
>>SK
>>Ollie Riches wrote:
>>
>>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" <skass@.drew.edu> wrote in message
>>news:421A3D93.8060100@.drew.edu...
>>
>>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" <dan.billow.remove@.monacocoach.removeme.com> wrote in
>>message news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@.microsoft.com...
>>
>>>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.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment