How to isolate a DBTYPE_DBTIMESTAMP to datetime error?

[afrotec2k]

Hi I am trying to extract data using SQL Server 2005 from a table on an ODBC linked server using the following syntax:

select *
from [Server]..[Schema].[Table]

When I run the query the following message appears:

'Error converting data type DBTYPE_DBTIMEST AMP to datetime'

I know that this is because a date or dates have been incorrectly input into the source database (e.g. 200-06-01 instead of 2008-06-01)

I would like to know how to locate/isolate this date field error via an SQL query in order to correct the date field in the source database.

Any ideas?

Thanks in advance

[ck9663]

What's the source? What kind of data source are you getting the data from? Can you specify on the select to convert that datetime column to varchar instead? You just have to find which column is offending the t-sql and you also need to enumerate the column names on your select not just "*".

-- CK

[afrotec2k]

What's the source? What kind of data source are you getting the data from? Can you specify on the select to convert that datetime column to varchar instead? You just have to find which column is offending the t-sql and you also need to enumerate the column names on your select not just "*".

-- CK

Hi

The source is a Progress database.

I've spoken to a colleague in the office and he suggested using a cursor to find the error, however when this employed, it returns a huge error message relating to various columns within the Progress database.

The error may be appearing because the Progress database (via the linked server) uses a different datestamp to SQL Server or because there is a problem with the link between the Progress database and SQL Server.

Not to worry. Thanks for your help.

[ck9663]

What error are you getting? Are you using OPENQUERY? Did you pass the query that will run in PROGRESS? Could you post what you have so far?

-- CK