RE: Search results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Wen

Unfortunately, the procedure is not working. When I tried it in SQL server
it's giving the following error message same as I mentioned earlier:

The UPDATE statement conflicted with the REFERENCE constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.SamplePrep", column 'sampleID'.

I don't get it. If I am using Begin Transaction then why it's conflicting
with the REFERENCE constraint?

Thanks for your help.

Manjree

"WenYuan Wang [MSFT]" wrote:

Hello Manjree,

Because the Update Query is in TRY-Catch, "RollBack Tran" in (if@@error<>0)
have no chance to be execute. When there is an error in the transaction,
stored procedure will pump to the Catch.

The correct way is that we should have to rollback the transaction in Catch.
The following code snippet will achieve what you need.

alter proc test_proc
as
BEGIN TRY
BEGIN TRANSACTION
Declare @returnMessage varchar(50)

set @returnMessage = '10'
UPDATE Sample
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

set @returnMessage = '11'
UPDATE SamplePrep
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

COMMIT TRANSACTION
Return 1

End Try
BEGIN CATCH
SELECT
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
If @errormessage = '10'
begin
Rollback tran
return 10
end
else
begin
rollback tran
return 11
end
END CATCH


Hope this helps. Please try the above method and let me know if this method
works for you. I'm glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


.

Relevant Pages

  • Re: computational model of transactions
    ... It seems to me easy enough to tell if an UPDATE statement references ... the appropriate choice of transaction isolation level. ... transactions to obtain a shared lock, but not the exclusive lock required to ...
    (comp.databases.theory)
  • Re: Deadlock Handling
    ... You can set the Lock_Timeout property to a given time period you are willing ... If that's exceeded you will get error message 1222 back. ... in the middle of a client side transaction, ... If success Then ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: column update order
    ... Robert Klemme wrote: ... The default transaction isolation level does not enforce that SELECTs, ... consistency but about column values referenced in an UPDATE statement. ... indicated that Oracle ensures read consistency within a transaction - ...
    (comp.databases.oracle.misc)
  • RE: Search results
    ... The error always be thrown SQL Server, even though we put the UPDATE query ... By design, the error message will be thrown after we executes the procedure. ... throwing the exception doesn't mean "Begin Transaction" is ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: deadlocks between single update statements ?
    ... Assume a transaction A with a single update statement updating many ... Assume another transaction B also with a single update, ... But how do I impose an order in an update statement? ...
    (comp.databases.oracle.server)