What’s the deal with transactions in a stored procedure called by an SSRS report?

Question

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?

Edit:

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.

asked 2016-03-01 by NReilingh

---

Answer

On the data source of an SSRS report, there is a property for “Use single transaction when processing the queries”. There are a couple different behaviors tied to marking this checkbox:

1) As you noticed, SSRS will use an explicit transaction to process the data sets using this data source, if this checkbox is marked. When it is not marked, there is no explicit transaction.

2) When this checkbox is not marked, data sets using the data source will execute in parallel, using separate connections to the server. If the checkbox is marked, then the data sets use a single connection and are processed serially.

Additionally, there may be different motivations for using this checkbox. The two major ones are:

1) Read consistency: If you are using Snapshot Isolation, you may want all data sets to be consistent to a single point in time. In this case, using a single explicit transaction and using the Snapshot isolation level can ensure all statements within the transaction are consistent to a single point in time.

2) Performance: Depending on a number of factors (which are really a separate topic), you may see dramatic performance difference between running the queries for different data sets in parallel vs running them serially.

answered 2016-08-05 by Andy Mallon