August 26, 2016 at 3:21 pm
I am trying to insert datainto a table calling a procedure.
INSERT dbo.TableA execute dbo.sp_ProcedureA
Stored procedure dbo.sp_ProcedureA has transaction and has codes within try/catch block.
I am running the same query in two different environments.
With same code, it runs well in one environment where as in another server, it throws the following error 'Cannot use the ROLLBACK statement within an INSERT-EXEC statement.'
Is this a database config issue, any suggestion? It had been working well before.
August 26, 2016 at 4:06 pm
Most likely in the first environment the conditions that result in the execution of the branch with the ROLLBACK are not being met, while in the second environment those conditions are being met.
Assuming your TRY...CATCH is attempting to ROLLBACK on some errors, then the second environment is encountering errors that the first environment does not.
I'd first figure out why the second environment is hitting errors that the first is not, but long-term you'll need to handle the errors differently if you want to avoid this.
Cheers!
August 26, 2016 at 11:15 pm
One way around this error is to harness temporary tables, because they have session-level scope.
You can create a #temporarytable before you EXEC sp_ProcedureA. You can then ALTER PROC sp_ProcedureA and add the statement INSERT #temporarytable <the results from sp_ProcedureA's SELECT statement>, within the body of sp_ProcedureA. After you EXEC sp_ProcedureA, the results will be in #temporarytable (unless a ROLLBACK happened).
Of course, above doesn't address cause :).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply