Unable to create the external table

Question

Unable to create the external table

MadhuVamsi 32

Hi team ,

I am unable to create an external table even when I created correct data source and file format , the table is creating but when reading the data facing the error as below -

Msg 107090, Level 16, State 1, Line 23
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: String or binary data would be truncated.

Can some one please help me with the error ,

Thanks in advance

Viorel 125.7K Reputation points

2022-08-04T19:29:03.253+00:00

Maybe you should specify the sizes, such as nvarchar(max) or nvarchar(100)?

Answer accepted by question author

1 additional answer

Your answer

Viorel 125.7K Reputation points

2022-08-04T19:29:03.253+00:00

Maybe you should specify the sizes, such as nvarchar(max) or nvarchar(100)?

Answer 1

Alberto Morillo 35,401 MVP Volunteer Moderator

You need to create a file format. For example:

CREATE EXTERNAL FILE FORMAT fmtFile  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,  
    FORMAT_OPTIONS (  
      FIELD_TERMINATOR = ',',  
      STRING_DELIMITER = '"',  
      FIRST_ROW = 2,  
      USE_TYPE_DEFAULT = TRUE,  
      ENCODING = 'UTF8' )  
);

Then on the definition of the External table you do the following:

FILE_FORMAT = fmtFile

MadhuVamsi 32 Reputation points

2022-08-05T12:09:56.777+00:00

Thanks Alberto , it worked after created the file format as you mentioned , Thanks
Alberto Morillo 35,401 Reputation points MVP Volunteer Moderator

2022-08-05T12:15:47.63+00:00

I am glad it worked. Thank you for letting me know the results and thank you for visiting Microsoft QA forums!

Answer 2

Isabellaz-1451 3,616

Hi @MadhuVamsi

Maybe you need to change the field type as nvarchar(MAX)
Please refer to this thread to see if field type is correct fit :
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-elt-data-loading#define-the-tables

Best Regards,
Isabella

Share via

Unable to create the external table

1 additional answer

Your answer