Error While updating Table using Linked Server

Question

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.

i don't have much ideal but below link seems helpful stackoverflow.com/questions/20388927/… — wiretext
– wiretext, Commented Jul 25, 2015 at 11:59

Alan · Accepted Answer · 2015-07-25 21:06:52Z

1

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.

answered Jul 25, 2015 at 21:06

Alan

3341 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2015-07-25 12:14:12Z

0

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

edited Jul 25, 2015 at 12:14

answered Jul 25, 2015 at 11:57

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

1 Comment

Alan Over a year ago

GETDATE() can actually be assigned to a smalldatetime field. you could try to assign it to a smalldatetime variable in SSMS. Even your link said that dates outside of 1900-2079 will pose problem. The problem only happens on linked server in SQL2008.

Collectives™ on Stack Overflow

Error While updating Table using Linked Server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related