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.