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

2021-08-24T06:33:55.057+00:00

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,443 questions
0 comments No comments
{count} votes

Accepted answer
  1. 2021-08-31T05:21:36.027+00:00

    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'
    
    3 people found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Alessandro 87 Reputation points
    2022-12-05T11:16:04.367+00:00

    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

    6 people found this answer helpful.

  2. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2021-08-25T19:57:38.333+00:00

    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

    2 people found this answer helpful.

  3. EBRU BADDAL 5 Reputation points
    2023-03-29T11:56:11.5833333+00:00

    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.

    1 person found this answer helpful.