This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
Problem
Consider the following scenario. You have a table in an Oracle which uses the Oracle date datatype:
create table OracleUser.table1 (c1 date);
You insert a row:
insert into OracleUser.table1 values(to_date('2019/12/23 18:59:59','yyyy/mm/dd hh24:mi:ss'));
You use the Azure Data Studio Data Virtualization extension to create a PolyBase external table in SQL Server 2019 database 'PB19Oracle19':
Choose Oracle
You will need to have already created the External Data Source in the EDS_PB19Oracle19 database. After that was created you will see it in the External Data Source drop down. Enter the Service name / SID (which is the SERVICE_NAME in tnsnames.ora on the server where Oracle is installed) and click Next.
Expand the external data source > tables and select the table (TABLE1) you are using as the Oracle source table and click Next.
Click Create.
After this, when you try to query the table, you get the following error:
Msg 7320, Level 16, State 110, Line 9
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Oracle Wire Protocol driver]Fractional truncation. Error in column 1. .
If you check the table definition, you'll see:
CREATE EXTERNAL TABLE [dbo].[TABLE1]
(
[C1] [DATE] NULL
)
WITH (DATA_SOURCE = [EDS_PB19Oracle19],LOCATION = N'[orcl.SQLPB.LOCAL].[ORACLEUSER].[TABLE1]')
Cause
SQL Server "DATE" datatype is incompatible with Oracle datatype "date". This is a bug in Azure Data Studio where incompatible datatype DATE is used in scripting the table definition. The compatible datatype in SQL Server is actually DATETIME2. Click here to see a list of data type mapping for SQL Server PolyBase feature (includes Oracle and other supported external data sources).
Resolution
To work around the issue, you can drop the table and recreate it with the correct datatype:
CREATE EXTERNAL TABLE [dbo].[TABLE1]
(
[C1] [DATETIME2] NULL
)
WITH (DATA_SOURCE = [EDS_PB19Oracle19],LOCATION = N'[orcl.SQLPB.LOCAL].[ORACLEUSER].[TABLE1]')
This bug has been fixed in the February 2020 release of Azure Data Studio. You can download and install Azure Data Studio and have the most recent release, or just start Azure Data Studio and you will be prompted to update.
More about this behavior
The bug is something that is fixed in Azure Data Studio to better align the data type mapping between SQL Server and Oracle. However, the fact that the table create was allowed by PolyBase is NOT a bug. Data types DATE, DATETIME, and DATETIME2 are all in the same family of data types. If the data from Oracle is being processed in the context of a data type in SQL Server and there is a loss of precision or truncation of the data, we will raise the "Fractional truncation" error, which is essentially a runtime error. PolyBase allows this behavior as it allows users to specify narrower types than the backend if they know the actual data isn't going to exceed the limitations of the type used in the external table definition.