Posts Tagged ‘SQLServer’

TADOStoredProc Parameter Returns wrong Value

Friday, December 9th, 2011

The other day I was trying to make a stored procedure call using hcOPF and kept getting a ‘parameter object is improperly defined’ error.  It has to be the most frustrating ADO error I’ve ever encountered, and I’ve come across a few in my time.  What makes it so frustrating is that the error message gives no indication of what parameter is the source of the problem.

As a developer, I would recommend you be as specific as possible when raising exceptions.  Imagine a stored proc taking 30 parameters, and the time it would take to figure out all the possible reasons why any one of them might cause this error when you have absolutely no idea why an API call you have made a thousand times before without a problem, now fails.

What I ended up doing is making the call manually in SQL Management Studio (SMS) to verify the back end was functioning,  Then I wrote a test application in Delphi that simply made the same call using a TADOStoredProc component with the same parameters I used in SMS.  The call succeeded, but one of the input/output values was not what I expected.  After experimenting at length, and discussing this situation with a co-worker, I discovered that the input/output parameter in question was a string defined as a varchar(max) in SQL Server, and it was being truncated at the length of the string I initially populated it with as an Input.  Once I verified this hypothesis, I filed a QC report (101665).

I probably should also have filed a report to fix the generic exception I encountered initially but I had already spent enough time isolating and filing the first bug report.  At some point you have to actually get some work done instead of helping fix someone else’s bugs ;-)