smalldatetime conversion

Question

smalldatetime conversion

UncleBoris

SSCrazy

Points: 2018
More actions
March 10, 2010 at 2:25 pm

#217521

Hello, maybe somebody can explain why the following is happening in SQL 2008?
For example.
There is a query that does an insert into a smalldatetime field server on a SQL 2000 or SQL 2005 server via a linked server.
i.e
-- table created on SQL 2000 Server
create table _sql2000table(col1 smalldatetime)
go
-- runs query from SQL 2008 to SQL 2000 via link
insert into SQL2000Server.database.dbo.SQL2000table(col1)
select getdate()
This worked fine when the executing server was SQL 2005.
But now that this SQL 2005 server has been upgraded to SQL 2008 an error alway occurs about overflow therefore a convert / cast is needed for the insert to work?
The OLE DB provider "SQLNCLI10" for linked server "SQL2000" could not INSERT INTO table "[SQL2000].[tempdb].[dbo].[_temp]" because of column "col1". Conversion failed because the data value overflowed the data type used by the provider.
SQL 2008 must work differently in this situation so maybe somebody can tell me what is different and / or why?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

UncleBoris SSCrazy Points: 2018 More actions · Answer 1

Sorry, just found this info that may help.

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

Posted by Microsoft on 1/07/2009 at 5:48 a.m.

Dear Customer,

Thanks again for your feedback regarding the "smalldatetime" column on a linked server. We appreciate the time and effort you have taken to report this problem to us.

We have investigated the problem and discovered a data type mapping problem. We have implemented a code fix which will be in available in the next release of SQL Server.

Until this fix is available, the workaround (as mentioned previously) is to cast the date to "small time" in the query.

If you are unable to utilize this workaround, please contact customer support services who can discuss additional options incl. requests for hotfixes in urgent cases.

Regards,

Joachim Hammer

Program Manager

SQL Server

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Thanks for the feedback on the fix, Joachim.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)