0

I'm new to polybase. I have linked my SQL Serer 2019 to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. In the documents the dates are defined as:

"created" : {
    "$date" : 1579540834768
},

In my external table i have the column defined as

[created] DATE,

I have tried to create the column as int and nvarchar(128) but the schema detection rejects it each time. (I have tried to create a field created_date but the schema detection also disagrees that this is correct.

When I try a query that returns any of the date fields I get this error:

Msg 105082, Level 16, State 1, Line 8
105082;Generic ODBC error: [Microsoft][Support] (40460) Fractional data truncated while performing conversion. .
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 8
Cannot fetch the rowset from OLE DB provider "MSOLEDBSQL" for linked server "(null)". .

This happens if i try and exclude null values in my query - even when filtering to specific records where the date is populated (validated using the Azure portal interface)

Is there something i should be doing to handle the integer date from the json records; or another type i can use to get my external table to work?

1

Found a solution. SQL Server recommends the wrong type for mongodb dates in the schema. Using DateTime2 resolved the issue. Found this on a polybase type mapping page in msdn.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.