SSRS: Dataset2 not showing data inserted in Dataset1

Question

I have two datasets namely Dataset1 and Dataset2.

Dataset1 is a query type of "Stored Procedure". The sp "TestProcpk" is selected and parameter "value" is mapped to it.

TestProcpk query:

Create procedure TestProcpk @value varchar(20)
as
insert into testProc select @value

Dataset2 uses the above table as below (Dataset2 fields are used in the report display):

select value from testProc
where value = @value

Expected

Note: table "testProc" is empty.

While running the report I select parameter value as "ABC". The report should display value "ABC".

Why Dataset2 is not reflecting the value "ABC" in same time? Any other workaround to achieve this.

Thanks

Answer 1

I believe your problem is due to SSRS running the transaction in parallel. The table isn't created from Dataset 1 when Dataset 2 is run.

In the Datasource Properties, on the General tab there is a setting for Use single transaction when processing queries. This forces the queries to run one at a time in a single transaction (great for using temp tables). Check this box and it should work as you expect. It will execute in the order of your datasets (top down).

For more info: http://blogs.msdn.com/b/robertbruckner/archive/2008/08/07/dataset-execution-order.aspx