Posts Tagged ‘ADO’

These are not the errors you are looking for….

Thursday, October 11th, 2012

Recently the DBA made some database changes in my current project and we started seeing a new error in the Eurekalog reports from my Delphi front-end.  The interesting thing is that the error message was actually a warning message from SQL Server 2008 R2.  The message is ‘Warning : null value was eliminated by an aggregate or other SET operation’.  The DBA had found from previous experience that ADO doesn’t always report the actual error, but a preceding message output to the console in a stored proc.

We decided to write a test stored proc and call it from Delphi, confirming his past experience.  I modfied my code to look through the ADO Error collection, and found the source of the exception.  As a result, I have modified hcOPF to provide access to the error collection via the new Errors function on the IhcStoredProc interface.  I have populated this TStringList in the event of an error with the ADO error collection messages.  The original Exception is re-raised, but now you can catch it, and access the actual source of the exception.  I have also stubbed out the DBX implementation.

An additional note about ADO; due to a sporadic bug we encountered with ADO I use the AddParameter method to create and populate stored procedure parameters through hcOPF.  The parameters must be created in the same order as they are defined in the stored procedure since ADO seems to ignore the parameter name when populating the proc parameters.  Using the stored procedure Refresh method followed by SetParamValue calls resulted in sporadic “Parameter @XXX not found” errors.  This appears to be a known issue with ADO that you can read about here.  Since making this change we have not had a single such error report.