CODEWITHSUNDEEP
sqlsql-serversql-update
Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Error While updating Table using Linked Server

I am trying to update a table in a server(ServerA) using a linked share connection from ServerB and i Got the below Error

The OLE DB provider "SQLNCLI10" for linked server "ServerA" could not UPDATE table "[ServerA].[MyDb].[dbo].[tbl_name]" because of column "CreateDt". Conversion failed because the data value overflowed the data type used by the provider.

This is the query I Used : 

update [ServerA].[MyDb].[dbo].[tbl_name]  
set transfer_fl = 1, 
    CreateDt = getdate()  
where transfer_fl<>'1'

the column CreateDt is of data type smalldatetime and it already has a value in it.

Can somebody give explain me why this happens.

Upvotes: 0

Views: 615

Answers (2)

Alan
Alan

Reputation: 334

This is a bug in SQL 2008/R2.

https://connect.microsoft.com/SQLServer/feedback/details/430745/sql-server-2008-unexpected-behavior-when-inserting-datetime-value-in-smalldatetime-column-on-a-linked-server

Unluckily it is marked as won't fix and you would have to do an explicit conversion.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

GETDATE (Transact-SQL)

Sintaxis

GETDATE ( )

Tipo de valor devuelto

datetime

no smalldatetime

Here is more detail explain of the SQL Server Error Messages - Msg 298

Upvotes: 0

Related Questions