This error , Cannot read the next data row for dataset Dataset1, is thrown for many reasons, at least I have seen it on two occasions.
First, when the dataset timed out (underlying connection closed) and the another one is when the datetime field was casted as character. Here I will show how to reproduce this error for the first scenario.
I have created a table called “Test” as below
Then created a report on top of this table to display the information in a Grid. The data set contains a plain SELECT statement without LOCKING hints
To mimic the timeout scenario, I have blocked the table by firing an update statement under BEGIN TRANSACTION
When I ran the report from BIDS, as expected, it was blocked by the update statement. Below is the screenshot from SP_WHO2 which shows that thread 57 was blocked by 51
As soon as, I killed this process , I could see the error thrown on the BIDS
The other occasion where it failed was when I was assigning a character date to DateTime and using it in a comparison
DECLARE @dt DATETIME
SET @dt = ‘2011-06-01’
I had to cast @dt to DateTime then it worked. Though this behaviour is not reproducible.