So, we recently had
a chestnut at work.
We have some
db-based error logging that was reporting a string truncation error upon
calling a specific sproc. Now, the error logging is written out by the
application not by the db itself. Our sprocs raise errors to the app (C#) and
the app is responsible for what it does with those errors. In this case, the error magically appeared on
a Saturday morning without an code/db changes. The app in question processes a
queue of data and every 30 seconds or so, it records the error.
So, I began doing
all the normal things. I started up Express Profiler from SSMS and started
trying to capture the bad data call. I ran profiler until a couple instances of
the error were record, then examined the trace. I couldn't find a single
instance where the strings passed were too big. WTF? We added User Error to the
trace and we could see the error call, but there wasn't a stored procedure call
around it. How could this be?
We did finally
identify which record in the queue was causing the issue, but we still couldn't
determine what bit of data was causing the issue b/c we couldn't find the call
in the trace? How could this be?
We fought with this
for several days until we finally figured it out: We are using table-type
parameters to pass data to the sproc. We added some additional logging to the
app to write out the values we are adding to the table types and presto! One of
them was passing a string exactly one character too long. But why weren't we
catching it in the profiler?
We are guessing that
the parameters are validated prior to the actually stored proc call (which
makes really good sense). The compiler blew up on the string that was too long,
but b/c the sproc call never happened, the compiler never saw it.
To be honest, I am
not a fan of table-types anyway, and this is just another behaviour that makes
me like them even less.