Using "Use Single Transaction to Process Queries" with Custom SSMS Report

Question

Post reply

Using "Use Single Transaction to Process Queries" with Custom SSMS Report

steveb25

SSC Veteran

Points: 248
More actions
September 7, 2016 at 9:19 am

#317068

Hi everyone,
I've been digging around for a reason/workaround to an issue I'm having, but cannot find anything, wondering if anyone has any ideas on here.
I am creating a report using BIDS/Data Tools (I've tested this for the 2008 and 2012 tools, and get the same behaviour), that I want to run as a custom report within SSMS (right click, Reports > Custom Report)
I want to define multiple datasets, and I want to run these in a specific order. I have done this previously using Reporting Services, by using the "Use Single Transaction to Process Queries" on the Data Source.
The issue I am experiencing is, when I use this option for a custom report I am rendering using Management Studio, the datasets do not return any results when I use the "Use Single Transaction" option. The moment I turn the option off, they start returning the data I expect, but then I can't guarantee they are being executed in the order I need.
Any idea why this is the case, and any ideas on a workaround? Any inspiration would be greatly appreciated!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Depending on your exact report, this may work - it works in principle, but performance might be an issue...

Create a hidden parameter that takes it's default value from Dataset1

Modify the query for Dataset2 to require a parameter (even if it's just WHERE @param = @param)

Set the dataset paramater value for Dataset2 to come from the hidden report parameter and the report should come out in the correct order.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

steveb25 SSC Veteran Points: 248 More actions · Answer 2

Thanks for the idea, unfortunately, it doesn't appear to work. For reference:

1. Created Parameter "ProcessingComplete"

2. Set Default Value as output from Dataset 1

3. Set Dataset 2 (which is query type SP) to require a parameter input into the SP

4. Set that parameter mapping to the "ProcessingComplete" variable

Unfortunately, SSMS comes back with the error "The "ProcessingComplete" parameter is missing a value.

Looks like it's still trying to process the second dataset before the output from the first dataset is complete.

Thanks very much for the suggestion!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Ah well, it was worth a bash...

I guess it is running the first query once for the parameter, then again for the report and that messes with your timing.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

If I were you, I would log this on Connect and Trello as a bug - I just tried it with a simple report that runs three selects that are not dependent on each other and it only renders the first table/dataset. It did run the queries in sequence (I had a waitfor delay in the first so I can see they ran sequentially in my trace) but it was the rendering that failed.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);