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.
create table tblTestDataTypeIssue
nEmpCode int identity(1,1),
Query for Inserting Data
@vFirstName = 'John'
,@vLastName = 'Le'
,@dDOJ = GETDATE()
insert into SQLVillage.WorkDB.dbo.tblTestDataTypeIssue (vFirstName, vLastName, dDOJ)
select @vFirstName, @vLastName, @dDOJ
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.
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.