String or binary data would be truncated while reading column of type 'VARCHAR(8000)'. Check ANSI_WARNINGS option.

Question

When querying the ss stream in azure-synapse-analytics, the fields in the stream are longer than 8000 and the query reports an error.

String or binary data would be truncated while reading column of type 'VARCHAR(8000)'. Check ANSI_WARNINGS option. File/External table name 'https://bingads-algo-prod-networkprotection-c08.azuredatalakestore.net/webhdfs/v1/shares/bingads.hm/local/NativeAds/PROD/Feeds/UserUnderstanding/SparkUserKeywordDailySS/2021/07/SparkUserKeyword_2021_07_29.ss', column 'finalKeywords'. Truncated value: '["80401999379142:-:cabins for rent in helen georgia:-:53F97AF01E24AEA6BE0E42E948F7A715:-:394325875:-:0.8183745:-:56","80401999379130:-:cabins for rent in helen georgia:-:53F97AF01E24AEA6BE0E42E948F7A715:-:394325875:-:0.8183745:-:65","80195819089033:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:94","80195775423189:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:96","80676880810840:-:vacation rentals in helen georgia:-:38A5D927C4010EF6C8E0A52BD06C19E7:-:394353342:-:0.78401875:-:100","80401999379149:-:cabins for rent in helen georgia:-:53F97AF01E24AEA6BE0E42E948F7A715:-:394325875:-:0.8183745:-:71","80676880810833:-:vacation rentals in helen georgia:-:38A5D927C4010EF6C8E0A52BD06C19E7:-:394353342:-:0.78401875:-:60","80401999379168:-:cabins for rent in helen georgia:-:53F97AF01E24AEA6BE0E42E948F7A715:-:394325875:-:0.8183745:-:69","71949585568264:-:helen ga rental:-:0FC8F5EF4060F1437949F52D174E843A:-:288181850:-:0.786925:-:53","80195775423179:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:123","80401999379124:-:cabins for rent in helen georgia:-:53F97AF01E24AEA6BE0E42E948F7A715:-:394325875:-:0.8183745:-:72","80195775423190:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:80","80195819089032:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:89","80195819089026:-:vacation rental in helen georgia:-:BB2E1520E16C3C741BD10F9BDA695D72:-:81451448:-:0.77999943:-:116","80401999379167:-:cabins for rent in ...
Total execution time: 00:00:07.343

What should be done?

Answer 1

I found a way to deal with this problem by using With to set the type and length of the field.

like this:

SELECT TOP 10 UserId,MUID,ANID,meta,RGUID,market,finalKeywords
FROM OPENROWSET(
BULK 'adl://bingads-algo-prod-networkprotection-c08.azuredatalakestore.net/shares/bingads.hm/local/NativeAds/PROD/Feeds/UserUnderstanding/SparkUserKeywordDailySS/2021/08/SparkUserKeyword_2021_08_18.ss', 
FORMAT = 'SStream',PARSER_VERSION = '2.0')
WITH(
    UserId VARCHAR(100),
    MUID VARCHAR(100),
    ANID VARCHAR(100),
    meta VARCHAR(500),
    RGUID VARCHAR(100),
    market VARCHAR(100),
    finalKeywords VARCHAR(MAX)
)
AS a WHERE UserId = '00000706F242688021A91791F30D696B'

Answer 2

Try Set the warning off if you just want to look into the data without defining the data types

SET ANSI_WARNINGS OFF
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK '',
FORMAT = 'DELTA'
) AS [result]
SET ANSI_WARNINGS ON

Answer 3

Hello @Andrew Huang (Beyondsoft Corporation) ,

Thanks for the ask and using the Microsoft Q&A platform .
This is a warning and I think that you can get around with this by creating the external table first . I think you can use varchar(max) .

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-data-types.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#example-create-external-table.

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 4

Whenever you see this message "string or binary data would be truncated" that means the field is NOT big enough to hold your data. For example, if the column data type is varchar(20) , and you try to put 50 characters in to it, you will get this error. Check the table structure.