Error Message

OLE DB provider "MSDASQL" for linked server "DBLABJ_NC_10_ODBC" returned message
"[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second
precision exceeds the scale specified in the parameter binding.".

Msg 7320, Level 16, State 2, Line 39
Cannot execute the query "SELECT FROM "dbHR"."dbo"."employee" "Tbl1005" WHERE "Tbl1005"."hiredate">?" against OLE DB provider "MSDASQL" for linked server
"DBLABJ_NC_10_ODBC".

Solution

Original Code

declare @hireDataDestMax datetime
declare @hireDateDefaultMax datetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Revised Code

declare @hireDataDestMax smalldatetime
declare @hireDateDefaultMax smalldatetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Explanation

Changed the definition of our local datetime variable from datetime to smalldatetime.

It seems that the Linked Server ODBC layer is not able to handle the more expansive datetime variable; and that we will have to use smalldatetime.