Re: curious problem in production
From: Ollie Riches (ollie.riches_at_phoneanalyser.net)
Date: 02/21/05
- Next message: MA: "Re: From working to not working :("
- Previous message: Steve Kass: "Re: curious problem in production"
- In reply to: Steve Kass: "Re: curious problem in production"
- Next in thread: IPGrunt: "Re: curious problem in production"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 20:54:19 -0000
fundamentally what you describe below is what is happening.
The actual order of events is this:
1. BizTalk recieves a message, it calls the primary consumer to process this
message. This consumer is a web service in the production environment.
2. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:
@P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657
3. It FAILS complaining about the unique key constraint. The exception/error
is returned biztalk via the web service.
4. BizTalk calls it's secondary consumer to process the message because the
primary consumer failed. The secondary consumer is a web service on my
development environment.
5. This web service calls the stored procedure on the database (data source
= 192.168.0.1, catalog FooDb) with:
@P1 bigint
set @P1=858
declare @P2 bigint
set @P2=776
declare @P3 varchar(1)
set @P3='Y'
exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1 output,
@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3 output,
@eps_session_sid = 2.085000000000000e+003, @session_month_code = N'3',
@session_year = 2005, @candidate_no = 1.250000000000000e+002, @centre_no =
N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
@component_ver_no = 0.000000000000000e+000, @candidate_uci =
N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender = N'M',
@candidate_dob = 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
@production_datetime = 'Feb 21 2005 4:21PM'
select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
2005-02-21 16:23:45.657
6. It Succeeds
I am currently away from my machine and unable to examine the rows in the
table. I will tomorrow. The point being is that when I use only the
development environment it works perfectly fine - i.e BizTalk just calls the
web service in the development environment.
Cheers for the help.
Ollie
"Steve Kass" <skass@drew.edu> wrote in message
news:421A46C9.9000109@drew.edu...
> So this is my understanding. Make sure you can reproduce this
> sequence.
>
> 1. There is a table candidateComponentEntries somewhere
>
> 2. Web service issues this statement to insert something (let us know
> how you are certain this is precisely what the web service issued,
> character for character)
>
> declare @P1 bigint
> set @P1=858
> declare @P2 bigint
> set @P2=776
> declare @P3 varchar(1)
> set @P3='Y'
> exec InsertCandidateQpEntry
> @candiateComponentEntries_id = @P1 output,
> @candidateQpMarkSets_id = @P2 output,
> @MarksAlreadyExists = @P3 output,
> @eps_session_sid = 2.085000000000000e+003,
> @session_month_code = N'3',
> @session_year = 2005,
> @candidate_no = 1.250000000000000e+002,
> @centre_no = N'57133',
> @business_stream_id = N'01',
> @assessment_code = N'2332',
> @assessment_ver_no = 1.000000000000000e+000,
> @component_id = N'01',
> @component_ver_no = 0.000000000000000e+000,
> @candidate_uci = N'571330030125F',
> @candidate_uci_type = N'UCI',
> @candidate_gender = N'M',
> @candidate_dob = 'May 30 1989 12:00AM',
> @qp_id = 1.000000000000000e+000,
> @production_datetime = 'Feb 21 2005 4:21PM'
>
> 3. You see this error:
> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert duplicate key
> in object 'candidateComponentEntries'
>
> 4. There is no additional row in candidateComponentEntries.
>
> 5. You paste this identical statement into Query Analyzer on your
> machine, which is connected to the same database, execute it,
> and it succeeds.
> 6. From the production server, you can now see the new row in
> the table candidateComponentEntries.
>
> Can you post the CREATE TABLE statement of candidateComponentEntries,
> and the definition of the unique constraint cce_uk1, and let us know
> what values for the columns of cce_uk1 should be inserted by the
> stored procedure? I assume you have looked at the table to find out
> whether there is a row matching those values, but tell us if there is,
> so we know whether you think the insert should fail or succeed in the
> first place.
>
> Do you really have a parameter called @candiateComponentEntries? If
> not, and you typed instead of cut and pasted, can you post everything
> by cutting and pasting so we know there are no typos?
>
> SK
>
>
>
>
>
> Ollie Riches wrote:
>
>>for the purposes of this test yes
>>
>>the database is being accessed from different machines (one is production
>>environment windows 2003 and the other is my development machine windows
>>XP (2002 SP1)) both using the .Net framework version 1.1 and ADO.Net via a
>>web service.
>>
>>hence the trace from sql profiler in the first message.
>>
>>Cheers
>>
>>Ollie
>>
>>
>>
>>
>>"Steve Kass" <skass@drew.edu> wrote in message
>>news:421A40EE.8040507@drew.edu...
>>
>>>Your production environment *is* your development environment
>>>and your development database *is* your production database?
>>>
>>>Are you accessing the database from different client machines? If
>>>so, it could be a collation issue, where string constants are interpreted
>>>differently.
>>>
>>>What do you mean when you say these procedure calls are "made"
>>>from different machines - are you typing them in and running them
>>>from Query Analyzer on separate machines connected to the same
>>>database or what?
>>>
>>>SK
>>>
>>>Ollie Riches wrote:
>>>
>>>
>>>>I mean it is physically the same machine and the same database.....
>>>>
>>>>so the database machine has an ip of 192.168.0.1
>>>>and the database is called FooDb
>>>>
>>>>The production environment is trying to insert into a database (ip
>>>>address 192.168.0.1) called FooDb - it FAILS
>>>>and the development environment is trying to insert into a database (ip
>>>>address 192.168.0.1) called FooDb - it SUCCEEDS
>>>>
>>>>ie. the database is the same machine
>>>>
>>>>hope that explains it more clearly...
>>>>
>>>>Cheers
>>>>
>>>>Ollie
>>>>
>>>>"Steve Kass" <skass@drew.edu> wrote in message
>>>>news:421A3D93.8060100@drew.edu...
>>>>
>>>>
>>>>>I'm confused. What do you mean they are inserting into the
>>>>>same database, if the two machines are not connected? How
>>>>>do you know the data in the table they are inserting into is
>>>>>identical on both machines? This error is saying something
>>>>>about the data already present in the table.
>>>>>
>>>>>If you know the data is the same, then maybe the collation
>>>>>differs between the two machines.
>>>>>
>>>>>Steve Kass
>>>>>Drew University
>>>>>
>>>>>Ollie Riches wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Thanks for the answer. Just to clarify there is no connection between
>>>>>>development and production machines. The problem is that when I run
>>>>>>the code from production it fails to insert into the database (lets
>>>>>>call it dbFoo) but when I run the same code from my development
>>>>>>machine against the SAME database (yes the one I called dbFoo earlier
>>>>>>in this sentence) IT SUCCEEDS. So Basically the same code is calling
>>>>>>the same stored procedure on the same database, in one environment it
>>>>>>fails and one it succeeds....
>>>>>>
>>>>>>Weird?
>>>>>>
>>>>>>Cheers
>>>>>>
>>>>>>Ollie
>>>>>>"Alien2_51" <dan.billow.remove@monacocoach.removeme.com> wrote in
>>>>>>message news:2AF7A354-54EC-4A94-814B-2F135A0DE82A@microsoft.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>I have questions about your design most specifically about the part
>>>>>>>where you
>>>>>>>put data into your development enviroment if you can't put it into
>>>>>>>your
>>>>>>>production enviroment. I'm assuming you have something like merge
>>>>>>>replication
>>>>>>>between these 2 environmets, if not how do you keep them in synch..?
>>>>>>>This
>>>>>>>type of scnerio can get very ugly, typically you see alot of PK
>>>>>>>violations.
>>>>>>>Why would you not wait in the BizTalk message box until you could put
>>>>>>>your
>>>>>>>data into production...? please post DDL/DML
>>>>>>>
>>>>>>>"Ollie Riches" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>I am having a problem call a sql server stored procedure in a
>>>>>>>>test\production environment. I am getting an exception from sql
>>>>>>>>server being
>>>>>>>>propagated back to the web service. The exception is a violation of
>>>>>>>>primary
>>>>>>>>key constraint. The exception message is:
>>>>>>>>
>>>>>>>>'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert
>>>>>>>>duplicate key
>>>>>>>>in object 'candidateComponentEntries'
>>>>>>>>
>>>>>>>>When I run the same code on the my development machine into the SAME
>>>>>>>>database it works perfectly fine. Yes the two different environments
>>>>>>>>are
>>>>>>>>trying to insert into the same sql server database. It is being via
>>>>>>>>ADO.Net
>>>>>>>>in an asp.net web service.
>>>>>>>>
>>>>>>>>Background:
>>>>>>>>A BizTalk process is calling a web service to insert\process some
>>>>>>>>data into
>>>>>>>>a sql server database and we have set it up to call my development
>>>>>>>>machine
>>>>>>>>if it fails in calling the production environment. Then we used the
>>>>>>>>sql
>>>>>>>>profiler to check the calls to the database and they produced the
>>>>>>>>following:
>>>>>>>>
>>>>>>>>Audit Login -- network protocol: TCP/IP
>>>>>>>>set quoted_identifier on
>>>>>>>>set implicit_transactions off
>>>>>>>>set cursor_close_on_commit off
>>>>>>>>set ansi_warnings on
>>>>>>>>set ansi_padding on
>>>>>>>>set ansi_nulls on
>>>>>>>>set concat_null_yields_null on
>>>>>>>>set language us_english
>>>>>>>>set dateformat mdy
>>>>>>>>set datefirst 7
>>>>>>>>.Net SqlClient Data Provider sa 0 53 2005-02-21 16:23:44.863
>>>>>>>>
>>>>>>>>RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider
>>>>>>>>sa 0 0
>>>>>>>>0 0 0 53 2005-02-21 16:23:44.873
>>>>>>>>
>>>>>>>>SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
>>>>>>>>COMMITTED;BEGIN
>>>>>>>>TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-21
>>>>>>>>16:23:44.873
>>>>>>>>RPC:Completed declare @P1 bigint
>>>>>>>>set @P1=858
>>>>>>>>declare @P2 bigint
>>>>>>>>set @P2=776
>>>>>>>>declare @P3 varchar(1)
>>>>>>>>set @P3='Y'
>>>>>>>>exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
>>>>>>>>output,
>>>>>>>>@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
>>>>>>>>output,
>>>>>>>>@eps_session_sid = 2.085000000000000e+003, @session_month_code =
>>>>>>>>N'3',
>>>>>>>>@session_year = 2005, @candidate_no = 1.250000000000000e+002,
>>>>>>>>@centre_no =
>>>>>>>>N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
>>>>>>>>@assessme
>>>>>>>>nt_ver_no = 1.000000000000000e+000, @component_id = N'01',
>>>>>>>>@component_ver_no
>>>>>>>>= 0.000000000000000e+000, @candidate_uci = N'571330030125F',
>>>>>>>>@candidate_uci_type = N'UCI', @candidate_gender = N'M',
>>>>>>>>@candidate_dob =
>>>>>>>>'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
>>>>>>>>@production_datetime
>>>>>>>>= 'Feb 21 2005 4:21PM'
>>>>>>>>select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
>>>>>>>>2005-02-21 16:23:44.883
>>>>>>>>
>>>>>>>>Audit Login -- network protocol: TCP/IP
>>>>>>>>set quoted_identifier on
>>>>>>>>set implicit_transactions off
>>>>>>>>set cursor_close_on_commit off
>>>>>>>>set ansi_warnings on
>>>>>>>>set ansi_padding on
>>>>>>>>set ansi_nulls on
>>>>>>>>set concat_null_yields_null on
>>>>>>>>set language us_english
>>>>>>>>set dateformat mdy
>>>>>>>>set datefirst 7
>>>>>>>>.Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
>>>>>>>>
>>>>>>>>SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net
>>>>>>>>SqlClient
>>>>>>>>Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893
>>>>>>>>
>>>>>>>>RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider
>>>>>>>>sa 0 0
>>>>>>>>0 0 0 59 2005-02-21 16:23:45.657
>>>>>>>>
>>>>>>>>SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
>>>>>>>>COMMITTED;BEGIN
>>>>>>>>TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-21
>>>>>>>>16:23:45.657
>>>>>>>>RPC:Completed declare @P1 bigint
>>>>>>>>set @P1=858
>>>>>>>>declare @P2 bigint
>>>>>>>>set @P2=776
>>>>>>>>declare @P3 varchar(1)
>>>>>>>>set @P3='Y'
>>>>>>>>exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
>>>>>>>>output,
>>>>>>>>@candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
>>>>>>>>output,
>>>>>>>>@eps_session_sid = 2.085000000000000e+003, @session_month_code =
>>>>>>>>N'3',
>>>>>>>>@session_year = 2005, @candidate_no = 1.250000000000000e+002,
>>>>>>>>@centre_no =
>>>>>>>>N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
>>>>>>>>@assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
>>>>>>>>@component_ver_no = 0.000000000000000e+000, @candidate_uci =
>>>>>>>>N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =
>>>>>>>>N'M',
>>>>>>>>@candidate_dob = 'May 30 1989 12:00AM', @qp_id =
>>>>>>>>1.000000000000000e+000,
>>>>>>>>@production_datetime = 'Feb 21 2005 4:21PM'
>>>>>>>>select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
>>>>>>>>2005-02-21 16:23:45.657
>>>>>>>>
>>>>>>>>SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider
>>>>>>>>sa 0 0 0
>>>>>>>>0 0 59 2005-02-21 16:23:45.657
>>>>>>>>
>>>>>>>>
>>>>>>>>The first call to the stored procedure 'InsertCandidateQpEntry' and
>>>>>>>>this
>>>>>>>>made from the production environment and the second call to this
>>>>>>>>stored
>>>>>>>>procedure is made from my development machine and this succeeds. It
>>>>>>>>appears
>>>>>>>>that the problem is not a code (my code) problem but maybe a problem
>>>>>>>>with
>>>>>>>>the framework.
>>>>>>>>
>>>>>>>>Production environment:
>>>>>>>>Windows 2003 Standard Edition
>>>>>>>>..Net Framework 1.1
>>>>>>>>
>>>>>>>>Development environemnt:
>>>>>>>>XP Pro (2002) SP 1
>>>>>>>>..Net Framework 1.1
>>>>>>>>
>>>>>>>>I even tried copying the development binaries to the production
>>>>>>>>server and
>>>>>>>>it still fails.....
>>>>>>>>
>>>>>>>>Any Ideas anyone?
>>>>>>>>
>>>>>>>>
>>>>>>>>Cheers in advance
>>>>>>>>
>>>>>>>>Ollie Riches
>>>>>>>>http://www.phoneanalyser.net
>>>>>>>>
>>>>>>>>Disclaimer: Opinions expressed in this forum are my own, and not
>>>>>>>>representative of my employer.
>>>>>>>>I do not answer questions on behalf of my employer. I'm just a
>>>>>>>>programmer
>>>>>>>>helping programmers.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
>>
- Next message: MA: "Re: From working to not working :("
- Previous message: Steve Kass: "Re: curious problem in production"
- In reply to: Steve Kass: "Re: curious problem in production"
- Next in thread: IPGrunt: "Re: curious problem in production"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
