6

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.

  • 1
    Someone deleted an answer (not sure why) asking if the datasource for my SSRS report could have had "Run all queries within a single transaction" checked. I hadn't thought of that, and I'm pretty sure it does! This might suggest that SSRS is creating a transaction at the API level that wouldn't be visible in the T-SQL trace, but I haven't tested this yet. I guess it's also possible that SSRS would do this for each query even if that datasource setting wasn't checked, but that doesn't help to answer the question. – NReilingh Aug 2 '16 at 4:26
  • Please update the question/post comment after you test. – Stoleg Aug 3 '16 at 12:55
1
+50

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.

0

In SSRS 2012, I did not encounter the same behavior indicated, here. My SPs ran inside transactions despite not having the box checked.

Checking the box had no effect on the transaction count. In both cases, @@TRANCOUNT was = 1.

  • Are you sure the report was actually re-running the query? This sounds like SSRS was reusing cached results, which can happen in VS. My testing was also on 2012. – NReilingh Jun 25 '18 at 20:58
  • After further analysis, I believe the Infor ERP that was calling the SSRS report was hijacking the Sp to do some of its own setup. So my results are likely invalid. – Dan Jun 26 '18 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.