Consider the following:
CREATE PROCEDURE dbo.usp_trantest AS SELECT @@TRANCOUNT as trancount; GO
When I call
usp_trantest manually from within SSMS, the trancount is 0. If I run an SSRS report that contains a dataset that queries the same stored procedure, the trancount is logged as 1.
Doing a T-SQL trace of the SSRS method shows a trace event for the stored procedure call that is identical to the one I did from SSMS.
Is there some behavior that could change based on the SSRS context? Like, an implicit transaction being opened for the dynamic sql call or something, or SSRS creating a transaction context outside of T-SQL?
In a now-deleted answer from someone (thanks, stranger!) it was suggested that the SSRS report's dataset might have had "Use single transaction when processing the queries" checked. This was indeed the case!
I did some further testing, and with this setting unchecked, the
@@TRANCOUNT is the same whether running in SSMS or from an SSRS report.
So it appears we can draw the conclusion that this Data Source setting does cause the SSRS report to create a transaction context on the database before running the queries. Since this extra transaction does not appear in the T-SQL trace, we can probably assume that it is being opened using an API method, instead of a T-SQL statement.