Results 1 to 13 of 13

Thread: Truncation Issue

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Truncation Issue

    I have a process that downloads some JSON from an API every night. The JSON is parsed into a datatable, then the datatable is written to a database. Very rarely, a mysterious issue arises. The process alerts me that data would be truncated.

    Normally, that wouldn't be much of a mystery. I have a field that is the wrong size for the data. Find the field, fix the issue. What makes it mysterious is that the download happens at midnight, when I'm asleep. I find out about the failure in the morning, go to try to identify the culprit...and the problem has gone away. This also happens only once every few months, so it's quite rare.

    I'm suspecting that what I'm seeing is some different kind of bug. It's possible that the data I'm downloading has changed between the midnight run of the process and when I try it again in the morning after a failure. It's possible, but it's not very likely. It would require somebody to upload some bad data the day before, then get up early, fix the mistake, and re-upload. They'd be in the same time zone, too, and I tend to get to it pretty early, so the window in which they'd have to find and fix the data is pretty narrow.

    I tried adding a bit of other decorations to suggest where the problem arises, but it doesn't work. The problem arises when the data is written to the DB. That's one Update statement. The hints were about data going into the datatable, which has all worked just fine.

    So, after all that, here's the question: If I am Updating a datatable to a database, and it fails with a complaint about data being too large and requiring truncation, is there a way to know which field the problem was related to, or even which row?
    Last edited by Shaggy Hiker; May 9th, 2023 at 04:39 PM.
    My usual boring signature: Nothing

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Truncation Issue

    So, after all that, here's the question: If I am Updating a datatable to a database, and it fails with a complaint about data being too large and requiring truncation, is there a way to know which field the problem was related to, or even which row?
    There may be a way to get that info back from the failure error message, but what I would probably do is add some code (that can easily be toggled on or off) that would record the received data to a simple text or other file so that you could examine it at a more convenient time to see what data you had at the time of the error.

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Truncation Issue

    One other thought is that maybe the data you are trying to get at midnight is in the process of being updated at midnight and you are only getting partial data, which may be causing the error. You could try getting the data at 12:30 am instead to see if this affects anything.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Truncation Issue

    I would start logging that data to text file and review daily for issues. Just ran into a similar problem here - the REGROUP API for text messages was enhanced and started hard rejecting invalid phone #'s with an HTML 500 error. Previously those bad phone #'s were eaten up by the API and ignore. Beneficial to know a bad phone # - but breaking existing code base is never beneficial.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Truncation Issue

    Are the two databases on the same version with the same fields?
    Also have u installed the latest patches?
    If it's a day to day issue turn every "candidate" for a night to nvarchar(max), see if it persists.
    Also review the SQL log.
    And finally have a look here:
    https://stackoverflow.com/questions/...ng-nvarcharmax
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Truncation Issue

    Quote Originally Posted by jdc2000 View Post
    There may be a way to get that info back from the failure error message, but what I would probably do is add some code (that can easily be toggled on or off) that would record the received data to a simple text or other file so that you could examine it at a more convenient time to see what data you had at the time of the error.
    Already doing that. That did give me an idea, though.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Truncation Issue

    Quote Originally Posted by jdc2000 View Post
    One other thought is that maybe the data you are trying to get at midnight is in the process of being updated at midnight and you are only getting partial data, which may be causing the error. You could try getting the data at 12:30 am instead to see if this affects anything.
    I was a bit lazy in writing that. It gets the data at 11:15. If that fails, it tries again an hour later, then again an hour after that. It failed three times.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Truncation Issue

    Quote Originally Posted by sapator View Post
    Are the two databases on the same version with the same fields?
    Also have u installed the latest patches?
    If it's a day to day issue turn every "candidate" for a night to nvarchar(max), see if it persists.
    Also review the SQL log.
    And finally have a look here:
    https://stackoverflow.com/questions/...ng-nvarcharmax
    They should be pretty much the same. I'm not sure about versions. When testing, I was running against a dev instance of SQL Server. I'm pretty sure that everything is the same between the two, but not entirely certain. I'll have to take a look.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Truncation Issue

    Well, the mystery deepens.

    I do write everything to a text file, then go from there. It wasn't strictly necessary, but it was really convenient for some testing, and I left it in place. When testing the problem today, I got a new download, thinking it would be the same as what I got during the night, but maybe not.

    Therefore, I went to get the file from the production machine, which was the one that got downloaded last night. I moved that to the development machine and tried importing it. That failed, and I was able to see exactly where and why. It was a simple case of a field not being large enough, exactly as the error suggested.

    But that leaves a bigger question that nobody here can answer: Why did I get that strange data during the night, but not during the day?

    As a bit of background, the download gets the last 180 days of records. This may be changing every day, or it may remain the same for a few months. In fact, for this particular download, it may remain the same for 11 months, then change a whole lot in the 12th month, which would be right about now. So, it should have been getting the same thing for a very long time, and only recently have started getting new records.

    What makes it such a puzzle is that the records that were causing the problem were from 2021. They're much more than 180 days back. They shouldn't have been delivered last night, and the reason everything was working when I tested this morning is because they weren't delivered this morning.

    That's very strange.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Truncation Issue

    Thanks to all of you. Guess I'll have to rep some other people for a change.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Truncation Issue

    I decided to unresolve this for a bit. The question is closely related, but slightly different: I have checked as much as I can and the evidence strongly suggests that the same bad record was downloaded each night since mid-April, but last night was the first night that I got an error about data being truncated.

    The DB logs the number of records written to the table, and it has remained the same for quite some time (that's more or less reasonable). What I can't confirm is that the record(s) with too much in that one field were included in each of those downloads. They SHOULD have been, as it was getting all records from the last 180 days, and the date on that one was April 17, but I can only confirm what showed up last night and what shows up today, and they are there in both of those.

    Is there any scenario where truncation doesn't throw an exception?
    My usual boring signature: Nothing

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Truncation Issue

    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Truncation Issue

    Semi-useful. It does explain one thing. I was thinking that I wouldn't get enough information to easily figure out the column causing the problem, which was true up until SQL Server 2019. Now, it shows enough information to make it trivial to find the issue.

    I was thinking there wasn't enough information, but there it was, so I was wondering if I had just missed it before. In fact, I had not, it wasn't available before. That's a nice feature to have.

    However, the article does mention a means to silently truncate, but it shouldn't apply in my case. I certainly wasn't changing how I was doing the insert from one time to the next. I wasn't changing anything at all. So, the mechanism is there, but I didn't invoke it. Did something else invoke it? If so, that's probably a bug.

    The mystery remains, though clues have been found.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width