curious problem in production

Discussion in 'ASP .Net Web Services' started by Ollie Riches, Feb 21, 2005.

  1. Ollie Riches

    Ollie Riches Guest

    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 Riches, Feb 21, 2005
    #1
    1. Advertising

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

    "Ollie Riches" <> wrote in message
    news:...
    > I am having a problem call a sql server stored procedure in a
    > test\production environment. I am getting an exception from sql server

    being
    > propagated back to the web service. The exception is a violation of

    primary
    > key constraint. The exception message is:
    >
    > 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
    > in object 'candidateComponentEntries'
    >
    > When I run the same code on the my development machine into the SAME
    > database it works perfectly fine. Yes the two different environments are
    > trying to insert into the same sql server database. It is being via

    ADO.Net
    > in an asp.net web service.
    >
    > Background:
    > A BizTalk process is calling a web service to insert\process some data

    into
    > a sql server database and we have set it up to call my development machine
    > if it fails in calling the production environment. Then we used the sql
    > profiler to check the calls to the database and they produced the

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

    0
    > 0 0 0 53 2005-02-21 16:23:44.873
    >
    > SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
    > TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
    > 16:23:44.873
    > RPC:Completed declare @P1 bigint
    > set @P1=858
    > declare @P2 bigint
    > set @P2=776
    > declare @P3 varchar(1)
    > set @P3='Y'
    > exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    > @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
    > @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    > @session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
    > N'57133', @business_stream_id = N'01', @assessment_code = N'2332',

    @assessme
    > nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    @component_ver_no
    > = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    > @candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob =
    > 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    @production_datetime
    > = 'Feb 21 2005 4:21PM'
    > select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    > 2005-02-21 16:23:44.883
    >
    > Audit Login -- network protocol: TCP/IP
    > set quoted_identifier on
    > set implicit_transactions off
    > set cursor_close_on_commit off
    > set ansi_warnings on
    > set ansi_padding on
    > set ansi_nulls on
    > set concat_null_yields_null on
    > set language us_english
    > set dateformat mdy
    > set datefirst 7
    > .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    >
    > SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net SqlClient
    > Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893
    >
    > RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0

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

    0
    > 0 0 59 2005-02-21 16:23:45.657
    >
    >
    > The first call to the stored procedure 'InsertCandidateQpEntry' and this
    > made from the production environment and the second call to this stored
    > procedure is made from my development machine and this succeeds. It

    appears
    > that the problem is not a code (my code) problem but maybe a problem with
    > the framework.
    >
    > Production environment:
    > Windows 2003 Standard Edition
    > .Net Framework 1.1
    >
    > Development environemnt:
    > XP Pro (2002) SP 1
    > .Net Framework 1.1
    >
    > I even tried copying the development binaries to the production server and
    > it still fails.....
    >
    > Any Ideas anyone?
    >
    >
    > Cheers in advance
    >
    > Ollie Riches
    > http://www.phoneanalyser.net
    >
    > Disclaimer: Opinions expressed in this forum are my own, and not
    > representative of my employer.
    > I do not answer questions on behalf of my employer. I'm just a programmer
    > helping programmers.
    >
    >
    >
    JohnnyAppleseed, Feb 21, 2005
    #2
    1. Advertising

  3. Ollie Riches

    Alien2_51 Guest

    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.
    >
    >
    >
    >
    Alien2_51, Feb 21, 2005
    #3
  4. Ollie Riches

    Ollie Riches Guest

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

    Ollie


    "JohnnyAppleseed" <> wrote in message
    news:...
    >A unique key constraint is a rule placed on a database table which
    >basically
    > states that a column or combination of column values cannot be repeated.
    > For
    > example, two employees cannot have the same social securoty number. Find
    > out
    > what columns constitute the unique constraint called 'cce_uk1', and then
    > determine under what conditions the programming is attempting to insert
    > the
    > a record with the same value twice.
    >
    > "Ollie Riches" <> wrote in message
    > news:...
    >> I am having a problem call a sql server stored procedure in a
    >> test\production environment. I am getting an exception from sql server

    > being
    >> propagated back to the web service. The exception is a violation of

    > primary
    >> key constraint. The exception message is:
    >>
    >> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate
    >> key
    >> in object 'candidateComponentEntries'
    >>
    >> When I run the same code on the my development machine into the SAME
    >> database it works perfectly fine. Yes the two different environments are
    >> trying to insert into the same sql server database. It is being via

    > ADO.Net
    >> in an asp.net web service.
    >>
    >> Background:
    >> A BizTalk process is calling a web service to insert\process some data

    > into
    >> a sql server database and we have set it up to call my development
    >> machine
    >> if it fails in calling the production environment. Then we used the sql
    >> profiler to check the calls to the database and they produced the

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

    > 0
    >> 0 0 0 53 2005-02-21 16:23:44.873
    >>
    >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
    >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
    >> 16:23:44.873
    >> RPC:Completed declare @P1 bigint
    >> set @P1=858
    >> declare @P2 bigint
    >> set @P2=776
    >> declare @P3 varchar(1)
    >> set @P3='Y'
    >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
    >> @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    >> @session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no
    >> =
    >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',

    > @assessme
    >> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    > @component_ver_no
    >> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    >> @candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob =
    >> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    > @production_datetime
    >> = 'Feb 21 2005 4:21PM'
    >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    >> 2005-02-21 16:23:44.883
    >>
    >> Audit Login -- network protocol: TCP/IP
    >> set quoted_identifier on
    >> set implicit_transactions off
    >> set cursor_close_on_commit off
    >> set ansi_warnings on
    >> set ansi_padding on
    >> set ansi_nulls on
    >> set concat_null_yields_null on
    >> set language us_english
    >> set dateformat mdy
    >> set datefirst 7
    >> .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    >>
    >> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net SqlClient
    >> Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893
    >>
    >> RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0

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

    > 0
    >> 0 0 59 2005-02-21 16:23:45.657
    >>
    >>
    >> The first call to the stored procedure 'InsertCandidateQpEntry' and this
    >> made from the production environment and the second call to this stored
    >> procedure is made from my development machine and this succeeds. It

    > appears
    >> that the problem is not a code (my code) problem but maybe a problem with
    >> the framework.
    >>
    >> Production environment:
    >> Windows 2003 Standard Edition
    >> .Net Framework 1.1
    >>
    >> Development environemnt:
    >> XP Pro (2002) SP 1
    >> .Net Framework 1.1
    >>
    >> I even tried copying the development binaries to the production server
    >> and
    >> it still fails.....
    >>
    >> Any Ideas anyone?
    >>
    >>
    >> Cheers in advance
    >>
    >> Ollie Riches
    >> http://www.phoneanalyser.net
    >>
    >> Disclaimer: Opinions expressed in this forum are my own, and not
    >> representative of my employer.
    >> I do not answer questions on behalf of my employer. I'm just a
    >> programmer
    >> helping programmers.
    >>
    >>
    >>

    >
    >
    Ollie Riches, Feb 21, 2005
    #4
  5. Ollie Riches

    Ollie Riches Guest

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

    Weird?

    Cheers

    Ollie
    "Alien2_51" <> wrote in message
    news:...
    >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.
    >>
    >>
    >>
    >>
    Ollie Riches, Feb 21, 2005
    #5
  6. Ollie Riches

    IPGrunt Guest

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

    > I am having a problem call a sql server stored procedure in a
    > test\production environment. I am getting an exception from sql

    server being
    > propagated back to the web service. The exception is a violation of

    primary
    > key constraint. The exception message is:
    >
    > 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert

    duplicate key
    > in object 'candidateComponentEntries'
    >
    > When I run the same code on the my development machine into the

    SAME
    > database it works perfectly fine. Yes the two different

    environments are
    > trying to insert into the same sql server database. It is being via

    ADO.Net
    > in an asp.net web service.
    >
    > Background:
    > A BizTalk process is calling a web service to insert\process some

    data into
    > a sql server database and we have set it up to call my development

    machine
    > if it fails in calling the production environment. Then we used the

    sql
    > profiler to check the calls to the database and they produced the

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

    sa 0 0
    > 0 0 0 53 2005-02-21 16:23:44.873
    >
    > SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    COMMITTED;BEGIN
    > TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-

    21
    > 16:23:44.873
    > RPC:Completed declare @P1 bigint
    > set @P1=858
    > declare @P2 bigint
    > set @P2=776
    > declare @P3 varchar(1)
    > set @P3='Y'
    > exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    output,
    > @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    output,
    > @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    N'3',
    > @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    @centre_no =
    > N'57133', @business_stream_id = N'01', @assessment_code = N'2332',

    @assessme
    > nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    @component_ver_no
    > = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    > @candidate_uci_type = N'UCI', @candidate_gender = N'M',

    @candidate_dob =
    > 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    @production_datetime
    > = 'Feb 21 2005 4:21PM'
    > select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    > 2005-02-21 16:23:44.883
    >
    > Audit Login -- network protocol: TCP/IP
    > set quoted_identifier on
    > set implicit_transactions off
    > set cursor_close_on_commit off
    > set ansi_warnings on
    > set ansi_padding on
    > set ansi_nulls on
    > set concat_null_yields_null on
    > set language us_english
    > set dateformat mdy
    > set datefirst 7
    > .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    >
    > SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net

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

    sa 0 0
    > 0 0 0 59 2005-02-21 16:23:45.657
    >
    > SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    COMMITTED;BEGIN
    > TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-

    21
    > 16:23:45.657
    > RPC:Completed declare @P1 bigint
    > set @P1=858
    > declare @P2 bigint
    > set @P2=776
    > declare @P3 varchar(1)
    > set @P3='Y'
    > exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    output,
    > @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    output,
    > @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    N'3',
    > @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    @centre_no =
    > N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
    > @assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
    > @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    > N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =

    N'M',
    > @candidate_dob = 'May 30 1989 12:00AM', @qp_id =

    1.000000000000000e+000,
    > @production_datetime = 'Feb 21 2005 4:21PM'
    > select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
    > 2005-02-21 16:23:45.657
    >
    > SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider

    sa 0 0 0
    > 0 0 59 2005-02-21 16:23:45.657
    >
    >
    > The first call to the stored procedure 'InsertCandidateQpEntry' and

    this
    > made from the production environment and the second call to this

    stored
    > procedure is made from my development machine and this succeeds. It

    appears
    > that the problem is not a code (my code) problem but maybe a

    problem with
    > the framework.
    >
    > Production environment:
    > Windows 2003 Standard Edition
    > .Net Framework 1.1
    >
    > Development environemnt:
    > XP Pro (2002) SP 1
    > .Net Framework 1.1
    >
    > I even tried copying the development binaries to the production

    server and
    > it still fails.....
    >
    > Any Ideas anyone?
    >
    >
    > Cheers in advance
    >
    > Ollie Riches
    > http://www.phoneanalyser.net
    >
    > Disclaimer: Opinions expressed in this forum are my own, and not
    > representative of my employer.
    > I do not answer questions on behalf of my employer. I'm just a

    programmer
    > helping programmers.
    >
    >
    >


    Ollie,

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

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

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

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

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

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

    Good luck.

    -- ipgrunt
    IPGrunt, Feb 21, 2005
    #6
  7. Ollie Riches

    Steve Kass Guest

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

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

    Steve Kass
    Drew University

    Ollie Riches wrote:

    >Thanks for the answer. Just to clarify there is no connection between
    >development and production machines. The problem is that when I run the code
    >from production it fails to insert into the database (lets call it dbFoo)
    >but when I run the same code from my development machine against the SAME
    >database (yes the one I called dbFoo earlier in this sentence) IT SUCCEEDS.
    >So Basically the same code is calling the same stored procedure on the same
    >database, in one environment it fails and one it succeeds....
    >
    >Weird?
    >
    >Cheers
    >
    >Ollie
    >"Alien2_51" <> wrote in message
    >news:...
    >
    >
    >>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.
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>

    >
    >
    >
    >
    Steve Kass, Feb 21, 2005
    #7
  8. Ollie Riches

    Ollie Riches Guest

    see inline....

    "IPGrunt" <> wrote in message
    news:Xns960482D36A6EDswiss127army001wrenc@130.133.1.4...
    > On 21 Feb 2005, "Ollie Riches" <>
    > postulated in news::
    >
    >> I am having a problem call a sql server stored procedure in a
    >> test\production environment. I am getting an exception from sql

    > server being
    >> propagated back to the web service. The exception is a violation of

    > primary
    >> key constraint. The exception message is:
    >>
    >> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert

    > duplicate key
    >> in object 'candidateComponentEntries'
    >>
    >> When I run the same code on the my development machine into the

    > SAME
    >> database it works perfectly fine. Yes the two different

    > environments are
    >> trying to insert into the same sql server database. It is being via

    > ADO.Net
    >> in an asp.net web service.
    >>
    >> Background:
    >> A BizTalk process is calling a web service to insert\process some

    > data into
    >> a sql server database and we have set it up to call my development

    > machine
    >> if it fails in calling the production environment. Then we used the

    > sql
    >> profiler to check the calls to the database and they produced the

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

    > sa 0 0
    >> 0 0 0 53 2005-02-21 16:23:44.873
    >>
    >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    > COMMITTED;BEGIN
    >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-

    > 21
    >> 16:23:44.873
    >> RPC:Completed declare @P1 bigint
    >> set @P1=858
    >> declare @P2 bigint
    >> set @P2=776
    >> declare @P3 varchar(1)
    >> set @P3='Y'
    >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    > output,
    >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    > output,
    >> @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    > N'3',
    >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    > @centre_no =
    >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',

    > @assessme
    >> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    > @component_ver_no
    >> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    >> @candidate_uci_type = N'UCI', @candidate_gender = N'M',

    > @candidate_dob =
    >> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    > @production_datetime
    >> = 'Feb 21 2005 4:21PM'
    >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    >> 2005-02-21 16:23:44.883
    >>
    >> Audit Login -- network protocol: TCP/IP
    >> set quoted_identifier on
    >> set implicit_transactions off
    >> set cursor_close_on_commit off
    >> set ansi_warnings on
    >> set ansi_padding on
    >> set ansi_nulls on
    >> set concat_null_yields_null on
    >> set language us_english
    >> set dateformat mdy
    >> set datefirst 7
    >> .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    >>
    >> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net

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

    > sa 0 0
    >> 0 0 0 59 2005-02-21 16:23:45.657
    >>
    >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    > COMMITTED;BEGIN
    >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-

    > 21
    >> 16:23:45.657
    >> RPC:Completed declare @P1 bigint
    >> set @P1=858
    >> declare @P2 bigint
    >> set @P2=776
    >> declare @P3 varchar(1)
    >> set @P3='Y'
    >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    > output,
    >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    > output,
    >> @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    > N'3',
    >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    > @centre_no =
    >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
    >> @assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
    >> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    >> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =

    > N'M',
    >> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =

    > 1.000000000000000e+000,
    >> @production_datetime = 'Feb 21 2005 4:21PM'
    >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
    >> 2005-02-21 16:23:45.657
    >>
    >> SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider

    > sa 0 0 0
    >> 0 0 59 2005-02-21 16:23:45.657
    >>
    >>
    >> The first call to the stored procedure 'InsertCandidateQpEntry' and

    > this
    >> made from the production environment and the second call to this

    > stored
    >> procedure is made from my development machine and this succeeds. It

    > appears
    >> that the problem is not a code (my code) problem but maybe a

    > problem with
    >> the framework.
    >>
    >> Production environment:
    >> Windows 2003 Standard Edition
    >> .Net Framework 1.1
    >>
    >> Development environemnt:
    >> XP Pro (2002) SP 1
    >> .Net Framework 1.1
    >>
    >> I even tried copying the development binaries to the production

    > server and
    >> it still fails.....
    >>
    >> Any Ideas anyone?
    >>
    >>
    >> Cheers in advance
    >>
    >> Ollie Riches
    >> http://www.phoneanalyser.net
    >>
    >> Disclaimer: Opinions expressed in this forum are my own, and not
    >> representative of my employer.
    >> I do not answer questions on behalf of my employer. I'm just a

    > programmer
    >> helping programmers.
    >>
    >>
    >>

    >
    > Ollie,
    >
    > If I offered suggestions, I'd be shooting in the dark, of course.
    > Just some ideas to think about.
    >
    > Are you using SQL or Windows authentication in the SQL server? Could
    > be an identity issue. What's the AD environment--domain based or
    > standalone?


    sql authentication

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


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


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

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


    Don't think so - it is not responsibility (they don't like developers
    looking at their databases :) )
    >
    > I don't want answers...just hoping to jar something loose in your
    > brain.


    nothing came loose.....

    >
    > Good luck.
    >
    > -- ipgrunt
    >
    Ollie Riches, Feb 21, 2005
    #8
  9. The error "'Violation of UNIQUE KEY constraint" has nothing to do with the
    ..NET framework or web services. It occurs when SQL Server prevents an insert
    from placing a duplicate value in a column that has a unique key constraint.
    Find out what is the unique key constraint and then fidn out why the same
    value would be inserted twice.

    "Ollie Riches" <> wrote in message
    news:...
    > thank for the lesson on unique keys, but I suggest you read the question
    > again because this is NOT what I am asking about.
    >
    > Ollie
    >
    >
    > "JohnnyAppleseed" <> wrote in message
    > news:...
    > >A unique key constraint is a rule placed on a database table which
    > >basically
    > > states that a column or combination of column values cannot be repeated.
    > > For
    > > example, two employees cannot have the same social securoty number. Find
    > > out
    > > what columns constitute the unique constraint called 'cce_uk1', and then
    > > determine under what conditions the programming is attempting to insert
    > > the
    > > a record with the same value twice.
    > >
    > > "Ollie Riches" <> wrote in message
    > > news:...
    > >> I am having a problem call a sql server stored procedure in a
    > >> test\production environment. I am getting an exception from sql server

    > > being
    > >> propagated back to the web service. The exception is a violation of

    > > primary
    > >> key constraint. The exception message is:
    > >>
    > >> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate
    > >> key
    > >> in object 'candidateComponentEntries'
    > >>
    > >> When I run the same code on the my development machine into the SAME
    > >> database it works perfectly fine. Yes the two different environments

    are
    > >> trying to insert into the same sql server database. It is being via

    > > ADO.Net
    > >> in an asp.net web service.
    > >>
    > >> Background:
    > >> A BizTalk process is calling a web service to insert\process some data

    > > into
    > >> a sql server database and we have set it up to call my development
    > >> machine
    > >> if it fails in calling the production environment. Then we used the sql
    > >> profiler to check the calls to the database and they produced the

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

    0
    > > 0
    > >> 0 0 0 53 2005-02-21 16:23:44.873
    > >>
    > >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
    > >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
    > >> 16:23:44.873
    > >> RPC:Completed declare @P1 bigint
    > >> set @P1=858
    > >> declare @P2 bigint
    > >> set @P2=776
    > >> declare @P3 varchar(1)
    > >> set @P3='Y'
    > >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    > >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
    > >> @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    > >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    @centre_no
    > >> =
    > >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',

    > > @assessme
    > >> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    > > @component_ver_no
    > >> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    > >> @candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob

    =
    > >> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    > > @production_datetime
    > >> = 'Feb 21 2005 4:21PM'
    > >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    > >> 2005-02-21 16:23:44.883
    > >>
    > >> Audit Login -- network protocol: TCP/IP
    > >> set quoted_identifier on
    > >> set implicit_transactions off
    > >> set cursor_close_on_commit off
    > >> set ansi_warnings on
    > >> set ansi_padding on
    > >> set ansi_nulls on
    > >> set concat_null_yields_null on
    > >> set language us_english
    > >> set dateformat mdy
    > >> set datefirst 7
    > >> .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    > >>
    > >> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net

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

    0
    > > 0
    > >> 0 0 0 59 2005-02-21 16:23:45.657
    > >>
    > >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN
    > >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-21
    > >> 16:23:45.657
    > >> RPC:Completed declare @P1 bigint
    > >> set @P1=858
    > >> declare @P2 bigint
    > >> set @P2=776
    > >> declare @P3 varchar(1)
    > >> set @P3='Y'
    > >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    > >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
    > >> @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    > >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    @centre_no
    > >> =
    > >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
    > >> @assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
    > >> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    > >> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =

    N'M',
    > >> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =

    1.000000000000000e+000,
    > >> @production_datetime = 'Feb 21 2005 4:21PM'
    > >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
    > >> 2005-02-21 16:23:45.657
    > >>
    > >> SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider sa

    0
    > >> 0

    > > 0
    > >> 0 0 59 2005-02-21 16:23:45.657
    > >>
    > >>
    > >> The first call to the stored procedure 'InsertCandidateQpEntry' and

    this
    > >> made from the production environment and the second call to this stored
    > >> procedure is made from my development machine and this succeeds. It

    > > appears
    > >> that the problem is not a code (my code) problem but maybe a problem

    with
    > >> the framework.
    > >>
    > >> Production environment:
    > >> Windows 2003 Standard Edition
    > >> .Net Framework 1.1
    > >>
    > >> Development environemnt:
    > >> XP Pro (2002) SP 1
    > >> .Net Framework 1.1
    > >>
    > >> I even tried copying the development binaries to the production server
    > >> and
    > >> it still fails.....
    > >>
    > >> Any Ideas anyone?
    > >>
    > >>
    > >> Cheers in advance
    > >>
    > >> Ollie Riches
    > >> http://www.phoneanalyser.net
    > >>
    > >> Disclaimer: Opinions expressed in this forum are my own, and not
    > >> representative of my employer.
    > >> I do not answer questions on behalf of my employer. I'm just a
    > >> programmer
    > >> helping programmers.
    > >>
    > >>
    > >>

    > >
    > >

    >
    >
    JohnnyAppleseed, Feb 21, 2005
    #9
  10. Ollie Riches

    Ollie Riches Guest

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

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

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

    ie. the database is the same machine

    hope that explains it more clearly...

    Cheers

    Ollie

    "Steve Kass" <> wrote in message
    news:...
    > I'm confused. What do you mean they are inserting into the
    > same database, if the two machines are not connected? How
    > do you know the data in the table they are inserting into is
    > identical on both machines? This error is saying something
    > about the data already present in the table.
    >
    > If you know the data is the same, then maybe the collation
    > differs between the two machines.
    >
    > Steve Kass
    > Drew University
    >
    > Ollie Riches wrote:
    >
    >>Thanks for the answer. Just to clarify there is no connection between
    >>development and production machines. The problem is that when I run the
    >>code from production it fails to insert into the database (lets call it
    >>dbFoo) but when I run the same code from my development machine against
    >>the SAME database (yes the one I called dbFoo earlier in this sentence) IT
    >>SUCCEEDS. So Basically the same code is calling the same stored procedure
    >>on the same database, in one environment it fails and one it succeeds....
    >>
    >>Weird?
    >>
    >>Cheers
    >>
    >>Ollie
    >>"Alien2_51" <> wrote in message
    >>news:...
    >>
    >>>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.
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>

    >>
    >>
    >>
    Ollie Riches, Feb 21, 2005
    #10
  11. Ollie Riches

    Ollie Riches Guest

    thanks for the lesson again.....

    Ollie

    "JohnnyAppleseed" <> wrote in message
    news:...
    > The error "'Violation of UNIQUE KEY constraint" has nothing to do with the
    > .NET framework or web services. It occurs when SQL Server prevents an
    > insert
    > from placing a duplicate value in a column that has a unique key
    > constraint.
    > Find out what is the unique key constraint and then fidn out why the same
    > value would be inserted twice.
    >
    > "Ollie Riches" <> wrote in message
    > news:...
    >> thank for the lesson on unique keys, but I suggest you read the question
    >> again because this is NOT what I am asking about.
    >>
    >> Ollie
    >>
    >>
    >> "JohnnyAppleseed" <> wrote in message
    >> news:...
    >> >A unique key constraint is a rule placed on a database table which
    >> >basically
    >> > states that a column or combination of column values cannot be
    >> > repeated.
    >> > For
    >> > example, two employees cannot have the same social securoty number.
    >> > Find
    >> > out
    >> > what columns constitute the unique constraint called 'cce_uk1', and
    >> > then
    >> > determine under what conditions the programming is attempting to insert
    >> > the
    >> > a record with the same value twice.
    >> >
    >> > "Ollie Riches" <> wrote in message
    >> > news:...
    >> >> I am having a problem call a sql server stored procedure in a
    >> >> test\production environment. I am getting an exception from sql server
    >> > being
    >> >> propagated back to the web service. The exception is a violation of
    >> > primary
    >> >> key constraint. The exception message is:
    >> >>
    >> >> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate
    >> >> key
    >> >> in object 'candidateComponentEntries'
    >> >>
    >> >> When I run the same code on the my development machine into the SAME
    >> >> database it works perfectly fine. Yes the two different environments

    > are
    >> >> trying to insert into the same sql server database. It is being via
    >> > ADO.Net
    >> >> in an asp.net web service.
    >> >>
    >> >> Background:
    >> >> A BizTalk process is calling a web service to insert\process some data
    >> > into
    >> >> a sql server database and we have set it up to call my development
    >> >> machine
    >> >> if it fails in calling the production environment. Then we used the
    >> >> sql
    >> >> profiler to check the calls to the database and they produced the
    >> > following:
    >> >>
    >> >> Audit Login -- network protocol: TCP/IP
    >> >> set quoted_identifier on
    >> >> set implicit_transactions off
    >> >> set cursor_close_on_commit off
    >> >> set ansi_warnings on
    >> >> set ansi_padding on
    >> >> set ansi_nulls on
    >> >> set concat_null_yields_null on
    >> >> set language us_english
    >> >> set dateformat mdy
    >> >> set datefirst 7
    >> >> .Net SqlClient Data Provider sa 0 53 2005-02-21 16:23:44.863
    >> >>
    >> >> RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider
    >> >> sa

    > 0
    >> > 0
    >> >> 0 0 0 53 2005-02-21 16:23:44.873
    >> >>
    >> >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
    >> >> COMMITTED;BEGIN
    >> >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
    >> >> 16:23:44.873
    >> >> RPC:Completed declare @P1 bigint
    >> >> set @P1=858
    >> >> declare @P2 bigint
    >> >> set @P2=776
    >> >> declare @P3 varchar(1)
    >> >> set @P3='Y'
    >> >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    >> >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
    >> >> output,
    >> >> @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    >> >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    > @centre_no
    >> >> =
    >> >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
    >> > @assessme
    >> >> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',
    >> > @component_ver_no
    >> >> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    >> >> @candidate_uci_type = N'UCI', @candidate_gender = N'M', @candidate_dob

    > =
    >> >> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
    >> > @production_datetime
    >> >> = 'Feb 21 2005 4:21PM'
    >> >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
    >> >> 2005-02-21 16:23:44.883
    >> >>
    >> >> Audit Login -- network protocol: TCP/IP
    >> >> set quoted_identifier on
    >> >> set implicit_transactions off
    >> >> set cursor_close_on_commit off
    >> >> set ansi_warnings on
    >> >> set ansi_padding on
    >> >> set ansi_nulls on
    >> >> set concat_null_yields_null on
    >> >> set language us_english
    >> >> set dateformat mdy
    >> >> set datefirst 7
    >> >> .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
    >> >>
    >> >> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net

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

    > 0
    >> > 0
    >> >> 0 0 0 59 2005-02-21 16:23:45.657
    >> >>
    >> >> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
    >> >> COMMITTED;BEGIN
    >> >> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-21
    >> >> 16:23:45.657
    >> >> RPC:Completed declare @P1 bigint
    >> >> set @P1=858
    >> >> declare @P2 bigint
    >> >> set @P2=776
    >> >> declare @P3 varchar(1)
    >> >> set @P3='Y'
    >> >> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
    >> >> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
    >> >> output,
    >> >> @eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
    >> >> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    > @centre_no
    >> >> =
    >> >> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
    >> >> @assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
    >> >> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    >> >> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =

    > N'M',
    >> >> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =

    > 1.000000000000000e+000,
    >> >> @production_datetime = 'Feb 21 2005 4:21PM'
    >> >> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
    >> >> 2005-02-21 16:23:45.657
    >> >>
    >> >> SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider sa

    > 0
    >> >> 0
    >> > 0
    >> >> 0 0 59 2005-02-21 16:23:45.657
    >> >>
    >> >>
    >> >> The first call to the stored procedure 'InsertCandidateQpEntry' and

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

    > with
    >> >> the framework.
    >> >>
    >> >> Production environment:
    >> >> Windows 2003 Standard Edition
    >> >> .Net Framework 1.1
    >> >>
    >> >> Development environemnt:
    >> >> XP Pro (2002) SP 1
    >> >> .Net Framework 1.1
    >> >>
    >> >> I even tried copying the development binaries to the production server
    >> >> and
    >> >> it still fails.....
    >> >>
    >> >> Any Ideas anyone?
    >> >>
    >> >>
    >> >> Cheers in advance
    >> >>
    >> >> Ollie Riches
    >> >> http://www.phoneanalyser.net
    >> >>
    >> >> Disclaimer: Opinions expressed in this forum are my own, and not
    >> >> representative of my employer.
    >> >> I do not answer questions on behalf of my employer. I'm just a
    >> >> programmer
    >> >> helping programmers.
    >> >>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
    Ollie Riches, Feb 21, 2005
    #11
  12. Ollie Riches

    Steve Kass Guest

    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" <> wrote in message
    >news:...
    >
    >
    >>I'm confused. What do you mean they are inserting into the
    >>same database, if the two machines are not connected? How
    >>do you know the data in the table they are inserting into is
    >>identical on both machines? This error is saying something
    >>about the data already present in the table.
    >>
    >>If you know the data is the same, then maybe the collation
    >>differs between the two machines.
    >>
    >>Steve Kass
    >>Drew University
    >>
    >>Ollie Riches wrote:
    >>
    >>
    >>
    >>>Thanks for the answer. Just to clarify there is no connection between
    >>>development and production machines. The problem is that when I run the
    >>>code from production it fails to insert into the database (lets call it
    >>>dbFoo) but when I run the same code from my development machine against
    >>>the SAME database (yes the one I called dbFoo earlier in this sentence) IT
    >>>SUCCEEDS. So Basically the same code is calling the same stored procedure
    >>>on the same database, in one environment it fails and one it succeeds....
    >>>
    >>>Weird?
    >>>
    >>>Cheers
    >>>
    >>>Ollie
    >>>"Alien2_51" <> wrote in message
    >>>news:...
    >>>
    >>>
    >>>
    >>>>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.
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>
    >>>
    >>>

    >
    >
    >
    >
    Steve Kass, Feb 21, 2005
    #12
  13. Ollie Riches

    IPGrunt Guest

    On 21 Feb 2005, "Ollie Riches" <>
    postulated in news:#8ifu$:

    > see inline....
    >
    > "IPGrunt" <> wrote in message
    > news:Xns960482D36A6EDswiss127army001wrenc@130.133.1.4...
    >> On 21 Feb 2005, "Ollie Riches" <>
    >> postulated in news::
    >>
    >>> I am having a problem call a sql server stored procedure in a
    >>> test\production environment. I am getting an exception from sql

    >> server being
    >>> propagated back to the web service. The exception is a violation

    of
    >> primary
    >>> key constraint. The exception message is:
    >>>
    >>> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert

    >> duplicate key
    >>> in object 'candidateComponentEntries'
    >>>
    >>> When I run the same code on the my development machine into the

    >> SAME
    >>> database it works perfectly fine. Yes the two different

    >> environments are
    >>> trying to insert into the same sql server database. It is being

    via
    >> ADO.Net
    >>> in an asp.net web service.
    >>>
    >>> Background:
    >>> A BizTalk process is calling a web service to insert\process some

    >> data into
    >>> a sql server database and we have set it up to call my

    development
    >> machine
    >>> if it fails in calling the production environment. Then we used

    the
    >> sql
    >>> profiler to check the calls to the database and they produced the

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

    16:23:44.863
    >>>
    >>> RPC:Completed exec sp_reset_connection .Net SqlClient Data

    Provider
    >> sa 0 0
    >>> 0 0 0 53 2005-02-21 16:23:44.873
    >>>
    >>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    >> COMMITTED;BEGIN
    >>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-

    02-
    >> 21
    >>> 16:23:44.873
    >>> RPC:Completed declare @P1 bigint
    >>> set @P1=858
    >>> declare @P2 bigint
    >>> set @P2=776
    >>> declare @P3 varchar(1)
    >>> set @P3='Y'
    >>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    >> output,
    >>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    >> output,
    >>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    >> N'3',
    >>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    >> @centre_no =
    >>> N'57133', @business_stream_id = N'01', @assessment_code =

    N'2332',
    >> @assessme
    >>> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',

    >> @component_ver_no
    >>> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    >>> @candidate_uci_type = N'UCI', @candidate_gender = N'M',

    >> @candidate_dob =
    >>> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,

    >> @production_datetime
    >>> = 'Feb 21 2005 4:21PM'
    >>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0

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

    16:23:44.893
    >>>
    >>> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net

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

    Provider
    >> sa 0 0
    >>> 0 0 0 59 2005-02-21 16:23:45.657
    >>>
    >>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ

    >> COMMITTED;BEGIN
    >>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-

    02-
    >> 21
    >>> 16:23:45.657
    >>> RPC:Completed declare @P1 bigint
    >>> set @P1=858
    >>> declare @P2 bigint
    >>> set @P2=776
    >>> declare @P3 varchar(1)
    >>> set @P3='Y'
    >>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1

    >> output,
    >>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3

    >> output,
    >>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =

    >> N'3',
    >>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,

    >> @centre_no =
    >>> N'57133', @business_stream_id = N'01', @assessment_code =

    N'2332',
    >>> @assessment_ver_no = 1.000000000000000e+000, @component_id =

    N'01',
    >>> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    >>> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender

    =
    >> N'M',
    >>> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =

    >> 1.000000000000000e+000,
    >>> @production_datetime = 'Feb 21 2005 4:21PM'
    >>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0

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

    Provider
    >> sa 0 0 0
    >>> 0 0 59 2005-02-21 16:23:45.657
    >>>
    >>>
    >>> The first call to the stored procedure 'InsertCandidateQpEntry'

    and
    >> this
    >>> made from the production environment and the second call to this

    >> stored
    >>> procedure is made from my development machine and this succeeds.

    It
    >> appears
    >>> that the problem is not a code (my code) problem but maybe a

    >> problem with
    >>> the framework.
    >>>
    >>> Production environment:
    >>> Windows 2003 Standard Edition
    >>> .Net Framework 1.1
    >>>
    >>> Development environemnt:
    >>> XP Pro (2002) SP 1
    >>> .Net Framework 1.1
    >>>
    >>> I even tried copying the development binaries to the production

    >> server and
    >>> it still fails.....
    >>>
    >>> Any Ideas anyone?
    >>>
    >>>
    >>> Cheers in advance
    >>>
    >>> Ollie Riches
    >>> http://www.phoneanalyser.net
    >>>
    >>> Disclaimer: Opinions expressed in this forum are my own, and not
    >>> representative of my employer.
    >>> I do not answer questions on behalf of my employer. I'm just a

    >> programmer
    >>> helping programmers.
    >>>
    >>>
    >>>

    >>
    >> Ollie,
    >>
    >> If I offered suggestions, I'd be shooting in the dark, of course.
    >> Just some ideas to think about.
    >>
    >> Are you using SQL or Windows authentication in the SQL server?

    Could
    >> be an identity issue. What's the AD environment--domain based or
    >> standalone?

    >
    > sql authentication



    OK, so the connection strings are identical?

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

    >
    >>
    >> I read your comment about it being a framework bug....how many

    times
    >> have I thought that myself, only to find something dumb (in my own
    >> stuff), later. Probably 99% certain it is NOT a framework bug.

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


    OK, you've been there, done that.


    >>
    >> BTW, what is the index: cce_uk1 ? Is it an autoincrement PK in
    >> candiateComponentEntries, or is it some kind of FK relation to
    >> another table? Is there some insert/update outside of a

    transaction
    >> causing a race condition (possible if the server is on the same
    >> physical processor as IIS/.NET.)?

    >
    > the point is I don't care about the keys, indexs or triggers it

    works when
    > calling from one environment why not from the other environment

    when it is
    > the SAME database.



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


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

    >
    > Don't think so - it is not responsibility (they don't like

    developers
    > looking at their databases :) )



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


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

    >
    > nothing came loose.....


    Keep shaking!

    >
    >>
    >> Good luck.


    ditto

    >>
    >> -- ipgrunt
    >>



    -- ipgrunt
    IPGrunt, Feb 21, 2005
    #13
  14. Ollie Riches

    Ollie Riches Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >>news:...
    >>
    >>>I'm confused. What do you mean they are inserting into the
    >>>same database, if the two machines are not connected? How
    >>>do you know the data in the table they are inserting into is
    >>>identical on both machines? This error is saying something
    >>>about the data already present in the table.
    >>>
    >>>If you know the data is the same, then maybe the collation
    >>>differs between the two machines.
    >>>
    >>>Steve Kass
    >>>Drew University
    >>>
    >>>Ollie Riches wrote:
    >>>
    >>>
    >>>>Thanks for the answer. Just to clarify there is no connection between
    >>>>development and production machines. The problem is that when I run the
    >>>>code from production it fails to insert into the database (lets call it
    >>>>dbFoo) but when I run the same code from my development machine against
    >>>>the SAME database (yes the one I called dbFoo earlier in this sentence)
    >>>>IT SUCCEEDS. So Basically the same code is calling the same stored
    >>>>procedure on the same database, in one environment it fails and one it
    >>>>succeeds....
    >>>>
    >>>>Weird?
    >>>>
    >>>>Cheers
    >>>>
    >>>>Ollie
    >>>>"Alien2_51" <> wrote in
    >>>>message news:...
    >>>>
    >>>>
    >>>>>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.
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>
    >>>>

    >>
    >>
    >>
    Ollie Riches, Feb 21, 2005
    #14
  15. Ollie Riches

    Ollie Riches Guest

    "IPGrunt" <> wrote in message
    news:Xns9604880B3CE1Dswiss127army001wrenc@130.133.1.4...
    > On 21 Feb 2005, "Ollie Riches" <>
    > postulated in news:#8ifu$:
    >
    >> see inline....
    >>
    >> "IPGrunt" <> wrote in message
    >> news:Xns960482D36A6EDswiss127army001wrenc@130.133.1.4...
    >>> On 21 Feb 2005, "Ollie Riches" <>
    >>> postulated in news::
    >>>
    >>>> I am having a problem call a sql server stored procedure in a
    >>>> test\production environment. I am getting an exception from sql
    >>> server being
    >>>> propagated back to the web service. The exception is a violation

    > of
    >>> primary
    >>>> key constraint. The exception message is:
    >>>>
    >>>> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert
    >>> duplicate key
    >>>> in object 'candidateComponentEntries'
    >>>>
    >>>> When I run the same code on the my development machine into the
    >>> SAME
    >>>> database it works perfectly fine. Yes the two different
    >>> environments are
    >>>> trying to insert into the same sql server database. It is being

    > via
    >>> ADO.Net
    >>>> in an asp.net web service.
    >>>>
    >>>> Background:
    >>>> A BizTalk process is calling a web service to insert\process some
    >>> data into
    >>>> a sql server database and we have set it up to call my

    > development
    >>> machine
    >>>> if it fails in calling the production environment. Then we used

    > the
    >>> sql
    >>>> profiler to check the calls to the database and they produced the
    >>> following:
    >>>>
    >>>> Audit Login -- network protocol: TCP/IP
    >>>> set quoted_identifier on
    >>>> set implicit_transactions off
    >>>> set cursor_close_on_commit off
    >>>> set ansi_warnings on
    >>>> set ansi_padding on
    >>>> set ansi_nulls on
    >>>> set concat_null_yields_null on
    >>>> set language us_english
    >>>> set dateformat mdy
    >>>> set datefirst 7
    >>>> .Net SqlClient Data Provider sa 0 53 2005-02-21

    > 16:23:44.863
    >>>>
    >>>> RPC:Completed exec sp_reset_connection .Net SqlClient Data

    > Provider
    >>> sa 0 0
    >>>> 0 0 0 53 2005-02-21 16:23:44.873
    >>>>
    >>>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
    >>> COMMITTED;BEGIN
    >>>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-

    > 02-
    >>> 21
    >>>> 16:23:44.873
    >>>> RPC:Completed declare @P1 bigint
    >>>> set @P1=858
    >>>> declare @P2 bigint
    >>>> set @P2=776
    >>>> declare @P3 varchar(1)
    >>>> set @P3='Y'
    >>>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
    >>> output,
    >>>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
    >>> output,
    >>>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =
    >>> N'3',
    >>>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,
    >>> @centre_no =
    >>>> N'57133', @business_stream_id = N'01', @assessment_code =

    > N'2332',
    >>> @assessme
    >>>> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',
    >>> @component_ver_no
    >>>> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
    >>>> @candidate_uci_type = N'UCI', @candidate_gender = N'M',
    >>> @candidate_dob =
    >>>> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
    >>> @production_datetime
    >>>> = 'Feb 21 2005 4:21PM'
    >>>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0

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

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

    > Provider
    >>> sa 0 0
    >>>> 0 0 0 59 2005-02-21 16:23:45.657
    >>>>
    >>>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
    >>> COMMITTED;BEGIN
    >>>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-

    > 02-
    >>> 21
    >>>> 16:23:45.657
    >>>> RPC:Completed declare @P1 bigint
    >>>> set @P1=858
    >>>> declare @P2 bigint
    >>>> set @P2=776
    >>>> declare @P3 varchar(1)
    >>>> set @P3='Y'
    >>>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
    >>> output,
    >>>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
    >>> output,
    >>>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =
    >>> N'3',
    >>>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,
    >>> @centre_no =
    >>>> N'57133', @business_stream_id = N'01', @assessment_code =

    > N'2332',
    >>>> @assessment_ver_no = 1.000000000000000e+000, @component_id =

    > N'01',
    >>>> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
    >>>> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender

    > =
    >>> N'M',
    >>>> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =
    >>> 1.000000000000000e+000,
    >>>> @production_datetime = 'Feb 21 2005 4:21PM'
    >>>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0

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

    > Provider
    >>> sa 0 0 0
    >>>> 0 0 59 2005-02-21 16:23:45.657
    >>>>
    >>>>
    >>>> The first call to the stored procedure 'InsertCandidateQpEntry'

    > and
    >>> this
    >>>> made from the production environment and the second call to this
    >>> stored
    >>>> procedure is made from my development machine and this succeeds.

    > It
    >>> appears
    >>>> that the problem is not a code (my code) problem but maybe a
    >>> problem with
    >>>> the framework.
    >>>>
    >>>> Production environment:
    >>>> Windows 2003 Standard Edition
    >>>> .Net Framework 1.1
    >>>>
    >>>> Development environemnt:
    >>>> XP Pro (2002) SP 1
    >>>> .Net Framework 1.1
    >>>>
    >>>> I even tried copying the development binaries to the production
    >>> server and
    >>>> it still fails.....
    >>>>
    >>>> Any Ideas anyone?
    >>>>
    >>>>
    >>>> Cheers in advance
    >>>>
    >>>> Ollie Riches
    >>>> http://www.phoneanalyser.net
    >>>>
    >>>> Disclaimer: Opinions expressed in this forum are my own, and not
    >>>> representative of my employer.
    >>>> I do not answer questions on behalf of my employer. I'm just a
    >>> programmer
    >>>> helping programmers.
    >>>>
    >>>>
    >>>>
    >>>
    >>> Ollie,
    >>>
    >>> If I offered suggestions, I'd be shooting in the dark, of course.
    >>> Just some ideas to think about.
    >>>
    >>> Are you using SQL or Windows authentication in the SQL server?

    > Could
    >>> be an identity issue. What's the AD environment--domain based or
    >>> standalone?

    >>
    >> sql authentication

    >
    >
    > OK, so the connection strings are identical?
    >
    > You're not using DPAPI to encrypt passwords, are you? This gives
    > machine specific results, I believe.
    >


    NO


    >>
    >>>
    >>> I read your comment about it being a framework bug....how many

    > times
    >>> have I thought that myself, only to find something dumb (in my own
    >>> stuff), later. Probably 99% certain it is NOT a framework bug.

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

    >
    > OK, you've been there, done that.
    >
    >
    >>>
    >>> BTW, what is the index: cce_uk1 ? Is it an autoincrement PK in
    >>> candiateComponentEntries, or is it some kind of FK relation to
    >>> another table? Is there some insert/update outside of a

    > transaction
    >>> causing a race condition (possible if the server is on the same
    >>> physical processor as IIS/.NET.)?

    >>
    >> the point is I don't care about the keys, indexs or triggers it

    > works when
    >> calling from one environment why not from the other environment

    > when it is
    >> the SAME database.

    >
    >
    > Can you really aford to exclude that half of the system so soon?
    > You've heard of thinking outside the box? The trick usually involves
    > finding a bigger box.
    >
    >
    >>
    >>>
    >>> Any triggers involved? (which can bite you from behind!)

    >>
    >> Don't think so - it is not responsibility (they don't like

    > developers
    >> looking at their databases :) )

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


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

    >>
    >> nothing came loose.....

    >
    > Keep shaking!


    getting a headache .....
    >
    >>
    >>>
    >>> Good luck.

    >
    > ditto
    >
    >>>
    >>> -- ipgrunt
    >>>

    >
    >
    > -- ipgrunt
    >
    Ollie Riches, Feb 21, 2005
    #15
  16. Ollie Riches

    Steve Kass Guest

    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" <> wrote in message
    >news:...
    >
    >
    >>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" <> wrote in message
    >>>news:...
    >>>
    >>>
    >>>
    >>>>I'm confused. What do you mean they are inserting into the
    >>>>same database, if the two machines are not connected? How
    >>>>do you know the data in the table they are inserting into is
    >>>>identical on both machines? This error is saying something
    >>>>about the data already present in the table.
    >>>>
    >>>>If you know the data is the same, then maybe the collation
    >>>>differs between the two machines.
    >>>>
    >>>>Steve Kass
    >>>>Drew University
    >>>>
    >>>>Ollie Riches wrote:
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>>Thanks for the answer. Just to clarify there is no connection between
    >>>>>development and production machines. The problem is that when I run the
    >>>>>code from production it fails to insert into the database (lets call it
    >>>>>dbFoo) but when I run the same code from my development machine against
    >>>>>the SAME database (yes the one I called dbFoo earlier in this sentence)
    >>>>>IT SUCCEEDS. So Basically the same code is calling the same stored
    >>>>>procedure on the same database, in one environment it fails and one it
    >>>>>succeeds....
    >>>>>
    >>>>>Weird?
    >>>>>
    >>>>>Cheers
    >>>>>
    >>>>>Ollie
    >>>>>"Alien2_51" <> wrote in
    >>>>>message news:...
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>>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.
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>
    >>>>>
    >>>
    >>>
    >>>

    >
    >
    >
    >
    Steve Kass, Feb 21, 2005
    #16
  17. Ollie Riches

    Ollie Riches Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >>news:...
    >>
    >>>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" <> wrote in message
    >>>>news:...
    >>>>
    >>>>
    >>>>>I'm confused. What do you mean they are inserting into the
    >>>>>same database, if the two machines are not connected? How
    >>>>>do you know the data in the table they are inserting into is
    >>>>>identical on both machines? This error is saying something
    >>>>>about the data already present in the table.
    >>>>>
    >>>>>If you know the data is the same, then maybe the collation
    >>>>>differs between the two machines.
    >>>>>
    >>>>>Steve Kass
    >>>>>Drew University
    >>>>>
    >>>>>Ollie Riches wrote:
    >>>>>
    >>>>>
    >>>>>
    >>>>>>Thanks for the answer. Just to clarify there is no connection between
    >>>>>>development and production machines. The problem is that when I run
    >>>>>>the code from production it fails to insert into the database (lets
    >>>>>>call it dbFoo) but when I run the same code from my development
    >>>>>>machine against the SAME database (yes the one I called dbFoo earlier
    >>>>>>in this sentence) IT SUCCEEDS. So Basically the same code is calling
    >>>>>>the same stored procedure on the same database, in one environment it
    >>>>>>fails and one it succeeds....
    >>>>>>
    >>>>>>Weird?
    >>>>>>
    >>>>>>Cheers
    >>>>>>
    >>>>>>Ollie
    >>>>>>"Alien2_51" <> wrote in
    >>>>>>message news:...
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>>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.
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>
    >>>>
    >>>>

    >>
    >>
    >>
    Ollie Riches, Feb 21, 2005
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Framework fan

    Curious link button navigation problem

    Framework fan, Mar 6, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    391
    Scott Allen
    Mar 8, 2004
  2. Rob Meade

    Curious Problem..

    Rob Meade, Apr 25, 2004, in forum: ASP .Net
    Replies:
    17
    Views:
    646
    Rob Meade
    Apr 27, 2004
  3. Replies:
    3
    Views:
    366
    Raymond DeCampo
    Aug 27, 2005
  4. nowhere man

    another curious problem with mozilla

    nowhere man, Nov 4, 2003, in forum: HTML
    Replies:
    4
    Views:
    406
    informant
    Nov 4, 2003
  5. Replies:
    1
    Views:
    397
Loading...

Share This Page