SQL Server – Linked Server /ODBC – Error – Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding (Msg 7320, Level 16, State 2, Line 39)

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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s