I had a strange problem last week in few SSRS Reports, scheduled in a report server.
This report uses StartTime and EndTime parameters defined as 'String' data type and the default value is populated from a dataset. (Format of String, 10/29/2010 6:00:00 AM)
The report uses a stored procedure (stored_proc) to pull the report data from the SQL Server database for display in the report.
The SP stored_proc uses 3 input parameters named below
It was worked well for the past one year, but suddenly it stop run in production and throws the below error.
Conversion failed when converting datetime from character string.
When we look at the SP, the below line of code throws the error(CreateDate is the database table column).
Where (CreateDate >= @StartTime AND CreateDate < @StopTime )
From the profiler log, we understand that the below statement was triggered by SSRS.
exec stored_proc @par1=N'4',@StartTime=N'10/29/2010 6:00:00 AM',@StopTime=N'10/29/2010 6:00:00 PM'
To resolve this problem, we have changed the data type of the StartTime and EndTime parameters to 'DateTime' in SSRS report, now the report works well. Below is the trace from the profiler, while we run the report
exec stored_proc @par1=N'4',@StartTime=''2010-10-19 06:00:00:000'',@StopTime=''2010-10-19 18:00:00:000''
We are analyzing what went wrong last for the report stop working suddenly, but still did not get any clue. The report and the stored procedure were not changed for the past one year.
We undrstand that the date parameter may be truncated due to length of the SP parameter (20 chars width).
Please share your thoughts on this, if you have any clue.
Thank you, JeyRead more...