sql server - SSRS: Dataset2 not showing data inserted in Dataset1 -

i have 2 datasets namely dataset1 , dataset2.

dataset1 query type of "stored procedure". sp "testprocpk" selected , parameter "value" mapped it.

testprocpk query:

create procedure testprocpk @value varchar(20) insert testproc select @value 

dataset2 uses above table below (dataset2 fields used in report display):

select value testproc value = @value 

expected

note: table "testproc" empty.

while running report select parameter value "abc". report should display value "abc".

why dataset2 not reflecting value "abc" in same time? other workaround achieve this.

thanks

i believe problem due ssrs running transaction in parallel. table isn't created dataset 1 when dataset 2 run.

in datasource properties, on general tab there setting use single transaction when processing queries. forces queries run 1 @ time in single transaction (great using temp tables). check box , should work expect. execute in order of datasets (top down).

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