locked
Insert from external table fails RRS feed

  • Question

  • Hi,

    I'm working with Azure SQL DWH and getting the following error while trying to insert into table from external table:

    SELECT *
    INTO    tmp_ext from external_table;

    Rows were rejected while reading from external source(s).

    1 row rejected from external table [EXT_XXX] in plan step 7 of query execution:

    Location: '/XXXXX.csv' Column ordinal: 97, Expected data type: VARCHAR(200) collate SQL_Latin1_General_CP1_CI_AS.

     

    Msg 35386, Level 16, State 1, Line 7

    Unable to allocate 272743 KB for columnstore compression because it exceeds the remaining memory from total allocated for current resource class and DWU. Please rerun query at a higher resource class, and also consider increasing DWU. See... for assistance.

    when i select from the external table all is good.

    How can i find which column and row is rejected?

    Thanks for help.

    Thursday, May 2, 2019 12:13 PM

Answers

  • Hi Gali,

    You can look at https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql and the option REJECTED_ROW_LOCATION on the external table to save the incorrect rows to a file.

    That will however not fix your error above. You will need to increase your resource class or scale up to be able to load that external file into a columnstore table.

    • Marked as answer by GaliM1 Thursday, May 2, 2019 3:24 PM
    Thursday, May 2, 2019 2:35 PM

All replies

  • Hi Gali,

    You can look at https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql and the option REJECTED_ROW_LOCATION on the external table to save the incorrect rows to a file.

    That will however not fix your error above. You will need to increase your resource class or scale up to be able to load that external file into a columnstore table.

    • Marked as answer by GaliM1 Thursday, May 2, 2019 3:24 PM
    Thursday, May 2, 2019 2:35 PM
  • Yeah, you need more Data Warehouse Units so will need to a scale up for this.
    Thursday, May 2, 2019 2:44 PM
  • Thank you for your answer.

    With the REJECTED_ROW_LOCATION i can see the value that rejected and it seems that the problem is with the STRING_DELIMITER.


    • Edited by GaliM1 Thursday, May 2, 2019 3:29 PM
    Thursday, May 2, 2019 3:28 PM
  • In this case it make no sense because i'm trying to read/load a small amount of records
    Thursday, May 2, 2019 3:32 PM
  • Loading into a columnstore table will always try to allocate enough memory to load at least 10 000 rows and with a very wide table that can require a lot of memory.

    Look into https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-ctas and use the CTAS command to load into a Heap table instead of a columnstore table if it is small.


    Thursday, May 2, 2019 3:51 PM