none
SQLSever 2016 - PolyBase - Datetime data RRS feed

  • Question

  • All,

    When I query an External Table which has a datetime column why is it giving me an error even though I was able to enter data into it successfully? This table is pointing to Azure Blob Storage.

    Msg 7320, Level 16, State 110, Line 28
    Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "(null)". Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.
    (/claim/QID639_20200107_154411_0.txt)Column ordinal: 2, Expected data type: DATETIME, Offending value: 2010-01-06 00:00:00.000  (Column Conversion Error), Error: Conversion failed when converting the NVARCHAR value '2010-01-06 00:00:00.000' to data type DATETIME.

    CREATE EXTERNAL TABLE dbo.wasbs_Claim
    ( 
        	ID int,
        	Claim VARCHAR(128),
            Claimdate datetime
    ) 
    WITH 
    ( 
        LOCATION = '/claim/',  -- see below comment 
        DATA_SOURCE = csvsource, 
        FILE_FORMAT = csvformat
    )
    GO
    insert into dbo.wasbs_Claim(ID,Claim,Claimdate) Values (1,'Automobile','01/06/2010') -- Will fail
    GO
    select * from dbo.wasbs_Claim

    The below T-SQL query goes through though:

    DECLARE @stringdate nvarchar(100)
    SET @stringdate = '2020-01-06 00:00:00.000'
    DECLARE @datetime datetime
    SET @datetime = @stringdate
    PRINT @datetime
    PRINT @stringdate

    Tuesday, January 7, 2020 8:53 PM

All replies

  • Hello Friend ,

    See the link below and see that you can format the data.


    FORMAT (Transact-SQL)


    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Wednesday, January 8, 2020 2:14 AM
  • Hi grajee,

    Because you do not specify a rejection option values when creating the external table, PolyBase uses the default values and the maximum reject threshold is 0 rows.

    When you retrieve data from an external data source, a data record is considered a "dirty" record if the actual data type or number of columns does not match the column definition of the external table. The default rejection option values is not allowed to select any "dirty" records.

    According to the error message, the NVARCHAR value '2010-01-06 00: 00: 00.000' cannot be converted to DATETIME when selecting.

    If you want to insert a datetime type data. you can use YYYYMMDD, or use CONVERT with the specific style.

    I think your insert statement can be changed to:

    insert into dbo.wasbs_Claim (ID, Claim, Claimdate) Values (1, 'Automobile', convert (datetime, '01 / 06/2010 '))
    or
    select id, claim, convert (datetime, claimdate) from dbo.wasbs_Claim

    Hope this could help you.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, January 8, 2020 2:29 AM
  • The issue is with the SELECT statement. The Insert goes through without any error. Here is the format that I created:

     CREATE EXTERNAL FILE FORMAT csvformat 
    WITH ( 
        FORMAT_TYPE = DELIMITEDTEXT, 
        FORMAT_OPTIONS ( 
            FIELD_TERMINATOR = ',',
    		DATE_FORMAT = 'MM/dd/yyyy'
    --		First_Row = 2 -- works only in Azure SQLDWH
        ) 
    );
    GO


    Incidentally, I'm able to use the same format definition against a source file that I copied to Azure Blob Storage. Below is the structure of the External Table I created against the emp.csv file. Against this external table I'm able to run the SELECT without any error..

    CREATE EXTERNAL TABLE polybaseblobemp -- Like a View on the Blob Storage file
    ( 
        	ID int,
        	eName VARCHAR(128),
            empdate datetime
    ) 
    WITH 
    ( 
        LOCATION = '/emp.csv',  -- see below comment 
        DATA_SOURCE = csvsource, 
        FILE_FORMAT = csvformat
    )


    Wednesday, January 8, 2020 3:09 AM
  • Hi grajee,

    run following select statement, it works?

    select id, claim, convert (datetime, claimdate) from dbo.wasbs_Claim

      LOCATION = '/claim/'         maybe you could specify  one .csv file in here, not the  /claim/QID639_20200107_154411_0.txt.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Wednesday, January 8, 2020 3:29 AM
  • Hi grajee,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 9, 2020 2:54 AM
  • QID639_20200107_154411_0.txt is automatically generated when I inserted data into the external table.

    The modified script did not work either.

    Thursday, January 9, 2020 3:07 AM
  • Hi grajee,

    what The modified script is ? you specify a .csv file in LOCATION = '/claim/'   ?

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 9, 2020 3:09 AM