Geeks With Blogs

News QTP and Stuff
Theo Moore Questions... Morphology? Longevity? Incept dates?

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. 

Posted on Tuesday, March 22, 2016 8:31 AM | Back to top


Comments on this post: Stored Procedure Call Failing, but nothing in Profiler?

Comments are closed.
Comments have been closed on this topic.
Copyright © Theo Moore | Powered by: GeeksWithBlogs.net