August 21, 2020 at 3:02 pm
Have a weird issue.
I inherited a set of databases that use lots of functions (mostly TVF). I know, bad, bad, bad. Have to live with it for now until I have time to rewrite.
Starting a few days ago, one particular function will fail almost every morning.
The function fails with 'String or binary data would be truncated'.
Function returns a table.
If I run the function SQL outside of the function, no error. If I run the function I get an error.
I have verified that the return table datatypes match with the source column datatypes and that the data being returned is within datatype definitions. I also verified that any data transforms are within datatype definitions.
If I recreate the function by either dropping/creating the function or altering the function (ALTER FUNCTION dbo.blah), the function suddenly starts to 'function' without throwing an error. FYI: this function has been running for at least 3 years without issue. Function has not been modified in 1+ years.
Anyone have any ideas on what could be causing this issue or ideas as to where I should start looking to diagnose this issue?
August 21, 2020 at 4:17 pm
Can you recreate the error?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2020 at 4:54 pm
Does the function use schema binding?
Please provide the text of the function, naturally first obscuring any names you need to protect.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 21, 2020 at 7:38 pm
I am not able to recreate the error.
The function does NOT use schema binding.
I have verified (at the source columns) that each item selected in this function has had no change in datatype or length.
I am not able to provide the code due to various Vendor related reasons.
Again, the code behind the function will run without error.
The function can be called without error.
At some point, the function simply stops working and fails with the error 'String or binary data would be truncated'. At this time, the code behind the function can be run without error, but the function will fail every time it is called until the function is recreated.
I am wondering if the function, metadata, query cache or ? is corrupting.
August 21, 2020 at 10:35 pm
I don't know, sounds a little fishy, but I could be wrong. Unfortunately, without seeing the actual code it may be difficult to help you figure out what is going wrong.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply