I have a stored procedure which calls 2 nested sub procedures. For Example,
MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN ;THROW END CATCH END
The insert stored procedure could insert a line with duplicate values violating a unique key constraint. I'm calling the main stored procedure from a .NET application. In case of unique key violation, my application is not receiving the correct exception. The exception received is 'Specified Cast is not valid'.
I've tried adding
SET XACT_ABORT ON but this did not correct the problem.