Relational Database Systems

Linked Server call behavior change in SQL Server 2008

Insert operation fails thru Linked Server in SQL Server 2008 if column in table defined as smalldatetime but variable declared in procedure or query is datetime.

Scenario:

Table Structure

create table tblTestDataTypeIssue
(
             nEmpCode int identity(1,1),
             vFirstName varchar(100),
             vLastName varchar(100),
             dDOJ smalldatetime
)

Query for Inserting Data

declare
@vFirstName varchar(100)
,@vLastName varchar(100)
,@dDOJ datetime

select
@vFirstName = 'John'
,@vLastName = 'Le'
,@dDOJ = GETDATE()

insert into SQLVillage.WorkDB.dbo.tblTestDataTypeIssue (vFirstName, vLastName, dDOJ)
select @vFirstName, @vLastName, @dDOJ
 

Execution Result

OLE DB provider "SQLNCLI10" for linked server "SQLVillage" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 13
The OLE DB provider "SQLNCLI10" for linked server "SQLVillage" could not INSERT INTO table "[SQLVillage].[WorkDB].[dbo].[tblTestDataTypeIssue]" because of column "dDOJ". Conversion failed because the data value overflowed the data type used by the provider.

 

Whereas same query works without any issue in SQL Server 2000 and SQL Server 2005.

Resolution:

Either variable needs to be declared as smalldatetime or needs to be casted as smalldatetime. Earlier version were supporting this implicit conversion whereas SQL Server 2008 has stopped supporting it and causing Query to fail. This is internal data type mapping algorithm issue for datetime/smalldatetime datatype. There is no alternative solution available at this moment however Microsoft may provide fix for the same as part of service pack / hotfix. Same call will run successfully if executed without linked server.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top