Hi Guys,
This statistics report has been running OK for a couple of years, recently the user encountered a problem with error below:
Crystal Report Viewer
Failed to open a rowset.
Details:ADO Error code: 0x80040e07
Source: Microsoft OLE DB Provider for SQL server
Description: Conversion failed when converting the nvarchar value '2 0 0 9' to data type int.
SQL State: 22018
Native Error:245
I don't believe this is report issue.
This report was written with CR 2008 and run by report viewer, it is run based on a view in the database server of SQL 2008.
In the SQL WHERE clause where I specified by using ISNUMERIC() to ensure that table's field returned are all valid:
ISNUMERIC(DTE.DATE1) = 1
DTE.DATE1 column is nvarchar while this column has lots of invalid data ( year) such as 'UUUU', '1UUU' , '2UUU', '20UU' etc.
I only want the data returned with correct four digit numeric value such as '2009', '1970', '2013'.
I have had a look at the DTE.DATE1 column and could not identify which row has caused the problme regarding '2009' particularly
If I remove the ISNUMERIC(), the query will be OK, but rubbish data are returned.
Could someone advise me if there is way to find the problem record.
Thanks in advace.
JS
IP Logged