Re: curious problem in production

From: Ollie Riches (ollie.riches_at_phoneanalyser.net)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 19:59:55 -0000

see inline....

"IPGrunt" <me@privacy.net> wrote in message
news:Xns960482D36A6EDswiss127army001wrenc@130.133.1.4...
> On 21 Feb 2005, "Ollie Riches" <ollie.riches@phoneanalser.net>
> postulated in news:e1SjhdDGFHA.1260@TK2MSFTNGP12.phx.gbl:
>
>> I am having a problem call a sql server stored procedure in a
>> test\production environment. I am getting an exception from sql
> server being
>> propagated back to the web service. The exception is a violation of
> primary
>> key constraint. The exception message is:
>>
>> 'Violation of UNIQUE KEY constraint 'cce_uk1'. Cannot insert
> duplicate key
>> in object 'candidateComponentEntries'
>>
>> When I run the same code on the my development machine into the
> SAME
>> database it works perfectly fine. Yes the two different
> environments are
>> trying to insert into the same sql server database. It is being via
> ADO.Net
>> in an asp.net web service.
>>
>> Background:
>> A BizTalk process is calling a web service to insert\process some
> data into
>> a sql server database and we have set it up to call my development
> machine
>> if it fails in calling the production environment. Then we used the
> sql
>> profiler to check the calls to the database and they produced the
> following:
>>
>> Audit Login -- network protocol: TCP/IP
>> set quoted_identifier on
>> set implicit_transactions off
>> set cursor_close_on_commit off
>> set ansi_warnings on
>> set ansi_padding on
>> set ansi_nulls on
>> set concat_null_yields_null on
>> set language us_english
>> set dateformat mdy
>> set datefirst 7
>> .Net SqlClient Data Provider sa 0 53 2005-02-21 16:23:44.863
>>
>> RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider
> sa 0 0
>> 0 0 0 53 2005-02-21 16:23:44.873
>>
>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
> COMMITTED;BEGIN
>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 53 2005-02-
> 21
>> 16:23:44.873
>> RPC:Completed declare @P1 bigint
>> set @P1=858
>> declare @P2 bigint
>> set @P2=776
>> declare @P3 varchar(1)
>> set @P3='Y'
>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
> output,
>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
> output,
>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =
> N'3',
>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,
> @centre_no =
>> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
> @assessme
>> nt_ver_no = 1.000000000000000e+000, @component_id = N'01',
> @component_ver_no
>> = 0.000000000000000e+000, @candidate_uci = N'571330030125F',
>> @candidate_uci_type = N'UCI', @candidate_gender = N'M',
> @candidate_dob =
>> 'May 30 1989 12:00AM', @qp_id = 1.000000000000000e+000,
> @production_datetime
>> = 'Feb 21 2005 4:21PM'
>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 31 0 0 0 53
>> 2005-02-21 16:23:44.883
>>
>> Audit Login -- network protocol: TCP/IP
>> set quoted_identifier on
>> set implicit_transactions off
>> set cursor_close_on_commit off
>> set ansi_warnings on
>> set ansi_padding on
>> set ansi_nulls on
>> set concat_null_yields_null on
>> set language us_english
>> set dateformat mdy
>> set datefirst 7
>> .Net SqlClient Data Provider sa 0 60 2005-02-21 16:23:44.893
>>
>> SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION .Net
> SqlClient
>> Data Provider sa 0 2 0 0 0 53 2005-02-21 16:23:44.893
>>
>> RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider
> sa 0 0
>> 0 0 0 59 2005-02-21 16:23:45.657
>>
>> SQL:BatchCompleted SET TRANSACTION ISOLATION LEVEL READ
> COMMITTED;BEGIN
>> TRANSACTION .Net SqlClient Data Provider sa 0 0 0 0 0 59 2005-02-
> 21
>> 16:23:45.657
>> RPC:Completed declare @P1 bigint
>> set @P1=858
>> declare @P2 bigint
>> set @P2=776
>> declare @P3 varchar(1)
>> set @P3='Y'
>> exec InsertCandidateQpEntry @candiateComponentEntries_id = @P1
> output,
>> @candidateQpMarkSets_id = @P2 output, @MarksAlreadyExists = @P3
> output,
>> @eps_session_sid = 2.085000000000000e+003, @session_month_code =
> N'3',
>> @session_year = 2005, @candidate_no = 1.250000000000000e+002,
> @centre_no =
>> N'57133', @business_stream_id = N'01', @assessment_code = N'2332',
>> @assessment_ver_no = 1.000000000000000e+000, @component_id = N'01',
>> @component_ver_no = 0.000000000000000e+000, @candidate_uci =
>> N'571330030125F', @candidate_uci_type = N'UCI', @candidate_gender =
> N'M',
>> @candidate_dob = 'May 30 1989 12:00AM', @qp_id =
> 1.000000000000000e+000,
>> @production_datetime = 'Feb 21 2005 4:21PM'
>> select @P1, @P2, @P3 .Net SqlClient Data Provider sa 0 30 0 0 0 59
>> 2005-02-21 16:23:45.657
>>
>> SQL:BatchCompleted COMMIT TRANSACTION .Net SqlClient Data Provider
> sa 0 0 0
>> 0 0 59 2005-02-21 16:23:45.657
>>
>>
>> The first call to the stored procedure 'InsertCandidateQpEntry' and
> this
>> made from the production environment and the second call to this
> stored
>> procedure is made from my development machine and this succeeds. It
> appears
>> that the problem is not a code (my code) problem but maybe a
> problem with
>> the framework.
>>
>> Production environment:
>> Windows 2003 Standard Edition
>> .Net Framework 1.1
>>
>> Development environemnt:
>> XP Pro (2002) SP 1
>> .Net Framework 1.1
>>
>> I even tried copying the development binaries to the production
> server and
>> it still fails.....
>>
>> Any Ideas anyone?
>>
>>
>> Cheers in advance
>>
>> Ollie Riches
>> http://www.phoneanalyser.net
>>
>> Disclaimer: Opinions expressed in this forum are my own, and not
>> representative of my employer.
>> I do not answer questions on behalf of my employer. I'm just a
> programmer
>> helping programmers.
>>
>>
>>
>
> Ollie,
>
> If I offered suggestions, I'd be shooting in the dark, of course.
> Just some ideas to think about.
>
> Are you using SQL or Windows authentication in the SQL server? Could
> be an identity issue. What's the AD environment--domain based or
> standalone?

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
>

Relevant Pages

  • Re: curious problem in production
    ... >> trying to insert into the same sql server database. ... >> if it fails in calling the production environment. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Migrating DTS Local Package from test to production environments v7.0
    ... let's you set just about every property in your package retrieving the value ... I create a database table which the values I want. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > package in a test environment and then moving it to a production ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Server 2005 Licensing concern.
    ... Making a Server-Client application database. ... Would our clients have to buy the Enterprise edition if we ... develop the SQL server in the DE? ... Workgroup test environment is to get an MSDN subscription, ...
    (comp.databases.ms-sqlserver)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I am in an environment where bulk insert isn't an option--even though the ... underlying database is SQL server, I am using a third party OLE DB provider-- ... > insert(assuming you are using SQL server) or DTS. ...
    (microsoft.public.data.ado)
  • curious problem in production
    ... I am getting an exception from sql server being ... When I run the same code on the my development machine into the SAME ... if it fails in calling the production environment. ... RPC:Completed declare @P1 bigint ...
    (microsoft.public.dotnet.framework.aspnet.webservices)