7

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.

Improve this question
2
  • 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
    Commented Aug 2, 2016 at 4:26
  • Please update the question/post comment after you test.
    – Stoleg
    Commented Aug 3, 2016 at 12:55

2 Answers 2

Reset to default
2
+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.

Improve this answer
2
  • Yeah but why should you NOT check it? Checking it can speed things up in some ill-defined situations but does unchecking it speed things up in others? I'm not sure what the pros and cons of this checkbox are.
    – user875234
    Commented Jan 10, 2022 at 15:46
  • Some reasons to leave it unchecked: jstnlowe.com/post/181881868504/… --- The report datasets or queries contain things like UPDATE statements that make changes to the data being returned. --- You want to reduce the amount of network traffic being generated. --- The report contains a large number of queries that when executed in parallel have an adverse effect on performance. --- There are licensing restrictions that limit the number of concurrent connections to the database
    – user875234
    Commented Jan 10, 2022 at 15:50
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.

Improve this answer
2
  • 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
    Commented Jun 25, 2018 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
    Commented Jun 26, 2018 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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