My rollback does not work

My rollback does not work

In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured.

How can I solve this problem. Thanks.

Below is my SP .

........

........

........

begin transaction

........

........

If Len(ltrim(rtrim(@.cmdS)))>0

execute(@.cmdS)

If Len(ltrim(rtrim(@.cmdS2)))>0

execute(@.cmdS2)

If Len(ltrim(rtrim(@.cmdS3)))>0

execute(@.cmdS3)

If Len(ltrim(rtrim(@.cmdS4)))>0

execute(@.cmdS4)

If Len(ltrim(rtrim(@.cmdS)))>0or Len(ltrim(rtrim(@.cmdS2)))>0or Len(ltrim(rtrim(@.cmdS3)))>0or Len(ltrim(rtrim(@.cmdS4)))>0

Begin

if@.@.ERROR <>0

begin

rollback transaction

set@.Hata ='Error !'

end

Else

Begin

set@.Hata ='Sucessfully executed :)'End

End

commit transaction

RETURN

It appears that the sprocs you "execute" are outside the transaction scope by default. I found some reference to using "begin distributed transaction", but you'll have to decide if that will work for you.