Linked Server. Remote SMALLDATETIME becomes local DATETIME?

Question

Linked Server. Remote SMALLDATETIME becomes local DATETIME?

Hans Lindgren

SSChampion

Points: 10496
More actions
June 22, 2005 at 7:58 am

#86650

Hello,
I have a distributed partitioned view. The local table has a column that is SMALLDATETIME, the remote tables have the same columns of type SMALLDATETIME. Creating the view that unites these tables and then doing a select from them, result in a queryplan where the local SMALLDATETIME gets converted to a DATETIME (infact, checking the meta data for the view, the columns are of DATETIME type and not SMALLDATETIME).
Is this supposed to happen and is there any more hidden 'upsizing' of column types when you are using linked servers?
Thanks, Hanslindgren.

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi Hanslindgren,

Did you try any other providers? I mean, there are 2 options:SQL Server and Other Data Source when creating Linked Server. In Other Data Source there are OLEDB providers for ODBC drivers and for SQL Server. It may be provider-related. What SQL Server version you are using?

Did you also try OPENROWSET ?

Yelena

Regards,Yelena Varsha

Hans Lindgren SSChampion Points: 10496 More actions · Answer 2

So far I have only tried on W2k Adv Server (fully patched, no IIS), SQL Server 2000 Enterprice Ed. SP4. 4-8 Processors. 4 GB Memory (No AWE)

The 2 providers I have tried are: SQL Server and Microsoft OLEDB provider for SQL Server. They give the same query plan. Trying some more tomorrow.

It seems like it is 'modifying' image, ntext, text, smalldatetime and smallmoney types.

Test:

CREATE TABLE [TestDistributedPartition] (

[myID] [int] NOT NULL ,

[myCol_bigint] [bigint] NULL ,

[myCol_binary] [binary] (50) NULL ,

[myCol_bit] [bit] NULL ,

[myCol_char] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[myCol_datetime] [datetime] NULL ,

[myCol_decimal] [decimal](18, 0) NULL ,

[myCol_float] [float] NULL ,

[myCol_image] [image] NULL ,

[myCol_int] [int] NULL ,

[myCol_money] [money] NULL ,

[myCol_nchar] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[myCol_ntext] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[myCol_numeric] [numeric](18, 0) NULL ,

[myCol_nvarchar] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[myCol_real] [real] NULL ,

[myCol_smalldatetime] [smalldatetime] NULL ,

[myCol_smallint] [smallint] NULL ,

[myCol_smallmoney] [smallmoney] NULL ,

[myCol_sql_variant] [sql_variant] NULL ,

[myCol_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[myCol_timestamp] [timestamp] NULL ,

[myCol_tinyint] [tinyint] NULL ,

[myCol_uniqueidentifier] [uniqueidentifier] NULL ,

[myCol_varbinary] [varbinary] (50) NULL ,

[myCol_varchar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [CK_TestDistributedPartition] CHECK ([myID] < 100)

)

CREATE VIEW TestPart AS

SELECT * FROM dbo.TestDistributedPartition

UNION ALL

SELECT * FROM myRemoteLinkedServer.tempdb.dbo.TestDistributedPartition

SELECT * FROM TestPart

With SQL Server:

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Concatenation

|--Compute Scalar(DEFINE[Expr1016]=[TestDistributedPartition].[myCol_image], [Expr1021]=[TestDistributedPartition].[myCol_ntext], [Expr1030]=[TestDistributedPartition].[myCol_text]))

| |--Compute Scalar(DEFINE[Expr1002]=Convert([TestDistributedPartition].[myCol_smalldatetime]), [Expr1003]=Convert([TestDistributedPartition].[myCol_smallmoney])))

| |--Table Scan(OBJECT[tempdb].[dbo].[TestDistributedPartition]))

|--Remote Query(SOURCEmyRemoteLinkedServer), QUERYSELECT Tbl1005."myID" "Col1037",Tbl1005."myCol_bigint" "Col1038",Tbl1005."myCol_binary" "Col1039",Tbl1005."myCol_bit" "Col1040",Tbl1005."myCol_char" "Col1041",Tbl1005."myCol_datetime" "Col1042",CONVERT(decimal(18,0),Tbl1005."myCol_decimal",0) "Expr1006",Tbl1005."myCol_float" "Col1044",Tbl1005."myCol_image" "Col1045",Tbl1005."myCol_int" "Col1046",Tbl1005."myCol_money" "Col1047",Tbl1005."myCol_nchar" "Col1048",Tbl1005."myCol_ntext" "Col1049",Tbl1005."myCol_numeric" "Col1050",Tbl1005."myCol_nvarchar" "Col1051",Tbl1005."myCol_real" "Col1052",Tbl1005."myCol_smalldatetime" "Col1053",Tbl1005."myCol_smallint" "Col1054",Tbl1005."myCol_smallmoney" "Col1055",Tbl1005."myCol_sql_variant" "Col1056",Tbl1005."myCol_text" "Col1057",Tbl1005."myCol_timestamp" "Col1058",Tbl1005."myCol_tinyint" "Col1059",Tbl1005."myCol_uniqueidentifier" "Col1060",Tbl1005."myCol_varbinary" "Col1061",Tbl1005."myCol_varchar" "Col1062" FROM "tempdb"."dbo"."TestDistPartiotion" Tbl1005))

With Microsoft OLEDB provider for SQL Server:

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Concatenation

|--Compute Scalar(DEFINE[Expr1016]=[TestDistributedPartition].[myCol_image], [Expr1021]=[TestDistributedPartition].[myCol_ntext], [Expr1030]=[TestDistributedPartition].[myCol_text]))

| |--Compute Scalar(DEFINE[Expr1002]=Convert([TestDistributedPartition].[myCol_smalldatetime]), [Expr1003]=Convert([TestDistributedPartition].[myCol_smallmoney])))

| |--Table Scan(OBJECT[tempdb].[dbo].[TestDistributedPartition]))

|--Remote Query(SOURCEmyRemoteLinkedServer), QUERYSELECT Tbl1005."myID" "Col1037",Tbl1005."myCol_bigint" "Col1038",Tbl1005."myCol_binary" "Col1039",Tbl1005."myCol_bit" "Col1040",Tbl1005."myCol_char" "Col1041",Tbl1005."myCol_datetime" "Col1042",CONVERT(decimal(18,0),Tbl1005."myCol_decimal",0) "Expr1006",Tbl1005."myCol_float" "Col1044",Tbl1005."myCol_image" "Col1045",Tbl1005."myCol_int" "Col1046",Tbl1005."myCol_money" "Col1047",Tbl1005."myCol_nchar" "Col1048",Tbl1005."myCol_ntext" "Col1049",Tbl1005."myCol_numeric" "Col1050",Tbl1005."myCol_nvarchar" "Col1051",Tbl1005."myCol_real" "Col1052",Tbl1005."myCol_smalldatetime" "Col1053",Tbl1005."myCol_smallint" "Col1054",Tbl1005."myCol_smallmoney" "Col1055",Tbl1005."myCol_sql_variant" "Col1056",Tbl1005."myCol_text" "Col1057",Tbl1005."myCol_timestamp" "Col1058",Tbl1005."myCol_tinyint" "Col1059",Tbl1005."myCol_uniqueidentifier" "Col1060",Tbl1005."myCol_varbinary" "Col1061",Tbl1005."myCol_varchar" "Col1062" FROM "tempdb"."dbo"."TestDistPartiotion" Tbl1005))

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hi,

I am confirming everything. I created and linked an alias to the same server. So I have the same table through linked server and without linked server depending on query.

select * from pubs.dbo.testtable1

UNION ALL

select * from pubs.dbo.testtable1

DateID CurrentDate

------ ---------------------

1 2005-05-05 01:02:00

select * from pubs.dbo.testtable1

Union All

select * from MyServer.pubs.dbo.testtable1

DateID CurrentDate

------ -------------------------

1 2005-05-05 01:02:00.000

As we can see in the linked server example with the same row of data we have seconds and milliseconds. I think it could be explained by the following. BOL article Data Type Mapping says:

Data Type Mapping

...........From OLE DB data types to SQL Server native data types. This conversion occurs when SQL Server reads data from the OLE DB data source, either in SELECT statements or in the reading side of UPDATE, INSERT, or in DELETE statements...........

So I checked OLEDB data types. Check out this article, you will find more surprises.There is a table for all types, I just will copy for the datetime and smalldatetime types

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_40oj.asp

OLE DB and SQL Server

Data Type Mapping in Rowsets and Parameters

SQL Server data type	SQLOLEDB data type

datetime

DBTYPE_DBTIMESTAMP

smalldatetime

DBTYPE_DBTIMESTAMP

Thanks for pointing our attention to this subject, I think you should write a short article so other DBAs and Developers will remember about it.

Yelena

Regards,Yelena Varsha