Linked SQL Server has error with converting data type DBTYPE_DBTIMESTAMP

Question

I have a SQL Server 2005 that has a 'Linked Server' to another SQL Server 2005.

When I run a simple query on 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]')

The W_PATIENT table does not have any 'TIMESTAMP' typed fields. It does have 5 DATETIME fields.

It is also import to note that I can query other tables with DATETIME values without an problem. The problem is focused on the W_PATIENT table.

Answer 1

Are you sure your linked server is set up correctly? What does this query yield:

SELECT product, provider FROM sys.servers WHERE name = 'Prod';

Answer 2

Is it possible that some of those DATETIME fields have "wrong entries", out of range etc. For example if one of those is actually a string with some "garbage" inside? Open query is executed on the remote server and only results are returned; while with select, data is transferred over so data-conversion is applied.

I would consider some data-profiling on the w_patient table. If you have SQL server 2008, there is a data profiling task in SSIS, but you can also find other tools to check for data quality.

Answer 3

jacksonakj,

I experienced similar problem today. Have been picking records from an Oracle database using a linked server for the past 1 year. Suddenly today (10 Aug 2010), I started getting the same error - error Converting data type DBTYPE_DBTIMESTAMP. I followed the use of OPENQUERY and IT WORKED. Thanks a million.