SSRS 2008 R2 Error There is no data for the field at position XX and data-set

Question

Hi,

I have SSRS 2008 R2 in SharePoint 2010 integration mode.

Few of the reports in SSRS generate below error message in ReportServer log file:

ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position XX

Background information on Report design:

 - Data-Source : Reports are having SQL 2008 R2 database as data-source.
 - Data-Sets : Reports are having multiple data-sets using SQL Store procedure (and SP parameter) 
 - The SQL Store procedure build logic utilises dynamic SQL, OPENQUERY functionality and EXEC syntax to execute assemble dynamic SQL.
 - SQL Server has a Oracle linked server connection that use by OPENQUERY functionality to execute remote SELECT query.

Now interesting when I execute SQL SP in SQL Management Studio is return data (depending on parameters pass to it) and fields and values are populating correctly in report data-set too. [Again SQL SP and Data-sets in Visual Studio both returns data successfully]

 - Now in of the report RDL file, report AutoRefresh property is set to 10 minutes. So when user run the report first time there is no field at position error in log file, however when report refresh every 10mins it logs above error. But in report there is no data is being missed. Then why log-file is saying missing field at position ?
- In other reoprt there is AutoRefresh property is set but in very random scenario report output miss few fields to display. I guess in that data-set doesn't execure proprly in SSRS report or some data-set fields is being missed ?
- Addition to that when I queried ReportServer database table 'dbo.ExecutionLogStorage' and 'Status' column has value rsSuccess for all runs. ?

I did some Bing search on this and found that when you uses dynamic SQL, OPENQUERY functionality and EXEC syntax to execute assemble dynamic SQL, SSRS data-set doesn't like it in very RANDOM scenario. Check-out below references to get more on this:

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/8f4e23bb-2e04-4e07-a914-894b6a9ff5e8/

http://bengribaudo.com/blog/2011/04/21/929/ssrs-dataset-field-list-is-empty-even-after-refresh-fields

Any idea? Thanks.

Answer 1

Please check out one of this thread:
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/8f4e23bb-2e04-4e07-a914-894b6a9ff5e8/

The above shows the dataset properties with the SQL query.

Above shows the fields list from the properties window. If a field is undefined here (it is in the fields list but the query doesn't populate it, Field Source not set) then you would get this error.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 2

Thanks for that. I have a question. Does Ordering of field list in data-set has to be same as Store procedure columns ordering?

Answer 3

I do not have a definitive answer for that question however it should hurt nothing to do so.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.