Linked SQL Server has error with converting data type DBTYPE_DBTIMESTAMP
sql-server-2005
Solution
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.
Problem
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.