"Mansoor Siddiqui" <MansoorSiddi***@discussions.microsoft.com> wrote in
message news:8E830AD0-7F5B-4EF1-95E0-818B6F2E8D2C@microsoft.com...
> Hi,
> I have an application that was working fine since last two months but all
> of
> a sudden it stopped working!!!
>
> This is what I do in my code:
>
> (1) Open the connection
> (2) Start the transaction and get the SQLTransaction object
> (3) Call ProcedureA to insert the parent record and get the newly
> generated id
> (4) Call ProcedureB to insert the child record and pass that procedure the
> parent id generated in step 3.
>
> and boom! the following error occurs:
>
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'p_p_fk2'.
> The conflict occurred in database 'MyDB', table 'ChildTable', column
> 'parent_id'.\r\nThe statement has been terminated."
>
> Looks like the 2nd procedure call does not see the newly generated id; it
> should see the id b/c both of the procedures are being run under the same
> transaction! since it does not see the id, hence I am thinking may be
> procedure are not running under the same transaction for some reason or
> may
> be some server setting changed last night which is causing this problem!
>
> Please note that the code has been working fine since last two months and
> stopped working all of a sudden!
>
> Can you guys think of anything that would have triggered this behavior.
> Please help.
>
> Thanks,
> Mansoor Siddiqui

"Marina Levit [MVP]" wrote:

> I don't think this is a transaction scope issue. At least it doesn't sound
> like one. If B was not running under the same transaction, you would
> typically get a deadlock scenario because B is waiting for A to commit, but
> of course A never does because the code is trying to run B.
>
> It sounds like you are calling A from code then B from code.  And it sounds
> like B does not get a valid parent_id. It sounds like either A is not
> correctly returnin the new parent_id, or the code is not correctly relaying
> that parent_id to B.
>
> Are you sure no one changed the code recently? Verify, that B is getting the
> correct parent_id.
>
> "Mansoor Siddiqui" <MansoorSiddi***@discussions.microsoft.com> wrote in
> message news:8E830AD0-7F5B-4EF1-95E0-818B6F2E8D2C@microsoft.com...
> > Hi,
> > I have an application that was working fine since last two months but all
> > of
> > a sudden it stopped working!!!
> >
> > This is what I do in my code:
> >
> > (1) Open the connection
> > (2) Start the transaction and get the SQLTransaction object
> > (3) Call ProcedureA to insert the parent record and get the newly
> > generated id
> > (4) Call ProcedureB to insert the child record and pass that procedure the
> > parent id generated in step 3.
> >
> > and boom! the following error occurs:
> >
> > INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'p_p_fk2'.
> > The conflict occurred in database 'MyDB', table 'ChildTable', column
> > 'parent_id'.\r\nThe statement has been terminated."
> >
> > Looks like the 2nd procedure call does not see the newly generated id; it
> > should see the id b/c both of the procedures are being run under the same
> > transaction! since it does not see the id, hence I am thinking may be
> > procedure are not running under the same transaction for some reason or
> > may
> > be some server setting changed last night which is causing this problem!
> >
> > Please note that the code has been working fine since last two months and
> > stopped working all of a sudden!
> >
> > Can you guys think of anything that would have triggered this behavior.
> > Please help.
> >
> > Thanks,
> > Mansoor Siddiqui
>
>
>

> Yep, you are right!
>
> After debugging and pulling hair for couple of hours, we came to know that
> one of our co-workers put a new trigger on the parent table and that messed
> up everything.
>
> Before adding that trigger to the parent table, ProcedureA used to return
> only one id, so we used SQLCommand.ExecuteScalar method;
>
> After adding that trigger, ProcedureA was returning two recordsets of one
> column each; first was from the trigger and the second (which used to be the
> first and the only recordset) was from the procedure itself! Since our
> ADO.NET code was calling ExecuteScalar, hence we were picking the wrong id
> and were using that to insert the child record, hence the foreign key error.
>
> Oh God, that was tricky to say the least and we had a tough time figure out
> whats going on. I learnt two lessons from that:
>
> (1) Don't use triggers @ all.
> (2) Use explicit output parameters for returning singular values from the
> procedure
>
> Thanks for your help.
>
> Mansoor Siddiqui
>
> "Marina Levit [MVP]" wrote:
>
>> I don't think this is a transaction scope issue. At least it doesn't sound
>> like one. If B was not running under the same transaction, you would
>> typically get a deadlock scenario because B is waiting for A to commit, but
>> of course A never does because the code is trying to run B.
>>
>> It sounds like you are calling A from code then B from code.  And it sounds
>> like B does not get a valid parent_id. It sounds like either A is not
>> correctly returnin the new parent_id, or the code is not correctly relaying
>> that parent_id to B.
>>
>> Are you sure no one changed the code recently? Verify, that B is getting the
>> correct parent_id.
>>
>> "Mansoor Siddiqui" <MansoorSiddi***@discussions.microsoft.com> wrote in
>> message news:8E830AD0-7F5B-4EF1-95E0-818B6F2E8D2C@microsoft.com...
>>> Hi,
>>> I have an application that was working fine since last two months but all
>>> of
>>> a sudden it stopped working!!!
>>>
>>> This is what I do in my code:
>>>
>>> (1) Open the connection
>>> (2) Start the transaction and get the SQLTransaction object
>>> (3) Call ProcedureA to insert the parent record and get the newly
>>> generated id
>>> (4) Call ProcedureB to insert the child record and pass that procedure the
>>> parent id generated in step 3.
>>>
>>> and boom! the following error occurs:
>>>
>>> INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'p_p_fk2'.
>>> The conflict occurred in database 'MyDB', table 'ChildTable', column
>>> 'parent_id'.\r\nThe statement has been terminated."
>>>
>>> Looks like the 2nd procedure call does not see the newly generated id; it
>>> should see the id b/c both of the procedures are being run under the same
>>> transaction! since it does not see the id, hence I am thinking may be
>>> procedure are not running under the same transaction for some reason or
>>> may
>>> be some server setting changed last night which is causing this problem!
>>>
>>> Please note that the code has been working fine since last two months and
>>> stopped working all of a sudden!
>>>
>>> Can you guys think of anything that would have triggered this behavior.
>>> Please help.
>>>
>>> Thanks,
>>> Mansoor Siddiqui
>>
>>