Bound SQL Server has error with DBTYPE_DBTIMESTAMP data type conversion

Question

I have SQL Server 2005 with a Linked Server with another SQL Server 2005.

When I run a simple query in one of the tables against the linked server:

SELECT TOP 10 [Pat_Id] FROM [Prod].[PIS].[dbo].[W_PATIENT] 

This error occurs:

 Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. 

However, when I use OPENQUERY, it works:

 SELECT * FROM OPENQUERY([Prod], 'SELECT TOP 10 [Pat_Id] FROM [PIS].[dbo].[W_PATIENT]') 

There are no TIMESTAMP fields entered in the W_PATIENT table. It has 5 DATETIME fields.

It should also be noted that I can query other tables with DATETIME values ​​without any problems. The problem is centered on the W_PATIENT table.

Answer 1

Is it possible that some of these DATETIME fields have "invalid entries", out of range, etc. For example, if one of them is actually a string with some garbage inside? An open query is executed on a remote server, and only results are returned; while with a choice, data is transmitted, therefore data conversion is applied.

I would consider some data profiling in the w_patient table. If you have SQL Server 2008, SSIS has the task of profiling data, but you can also find other tools for checking data quality.