August 29, 2018 at 7:12 am
We have recently migrated our reporting server over to Azure. We have a single report that is failing with the exception...
processing!ReportServer_0-76!1864!08/16/2018-15:10:03:: w WARN: Data source 'ReportDBDataSource': Report processing has been aborted.
processing!ReportServer_0-76!1864!08/16/2018-15:10:03:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DatasetName'. ---> System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.
If you execute the associated stored procedure from the DB then it runs without any issues. The same stored procedure and rdl file used on the old environment also works without any problems, so I am under the assumption that there must be an environment related problem here, possibly involving date formatting.
The stored procedure generates a single row, single column output in date format, which is essentially the following...
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2018-08-20 00:00:00.000') - 5, 0)
Any ideas would be much appreciated?
September 4, 2018 at 10:57 am
ben.hardy - Wednesday, August 29, 2018 7:12 AMWe have recently migrated our reporting server over to Azure. We have a single report that is failing with the exception...processing!ReportServer_0-76!1864!08/16/2018-15:10:03:: w WARN: Data source 'ReportDBDataSource': Report processing has been aborted.
processing!ReportServer_0-76!1864!08/16/2018-15:10:03:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DatasetName'. ---> System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.If you execute the associated stored procedure from the DB then it runs without any issues. The same stored procedure and rdl file used on the old environment also works without any problems, so I am under the assumption that there must be an environment related problem here, possibly involving date formatting.
The stored procedure generates a single row, single column output in date format, which is essentially the following...
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2018-08-20 00:00:00.000') - 5, 0)Any ideas would be much appreciated?
Regional settings generally affect those types of things. If there is anyway, try to use a more widely accepted format such as YYYYMMDD
Sue
September 5, 2018 at 3:06 am
Thank you Sue. It was in fact the locale setting on the server that triggered the exception but not the root cause of the problem.
It was an old report where someone had used a hard coded date in US format as a default value for the dataset. The date was set as '05/28/1984', so when the string was passed to the associated stored procedure to convert it to a datetime value it was reading it as the 5th day of the 28th month and failing. To avoid messing around with it too much I have just updated the value to 01/01/1900 to ensure it works with all regional settings.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply