Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Bob Swart (aka Drs.Bob) Dr.Bob's Delphi Clinics Dr.Bob's Delphi Courseware Manuals
View Bob Swart's profile on LinkedIn Drs.Bob's Delphi Notes
These are the voyages using Delphi Enterprise (and Architect). Its mission: to explore strange, new worlds. To design and build new applications. To boldly go...

SQL Server / MSDE Stored Procedures with BDP

Author: Bob Swart
Posted: 8/30/2005 3:27:54 PM (GMT+1)

Yesterday and today I was working on a new article for The Delphi Magazine about the use of Stored Procedures in Delphi (read the October issue for the full story).
I use SQL Server / MSDE with the Northwind database as playground for my examples. All Stored Procedures in the Northwind database return a result, but funnily enough when I used the new Stored Procedure dialog of Delphi 2005 to execute them, I only got empty results.
Calling the Stored Procedures using a BdpCommand did give the expected result, with the exception of a Stored Procedure that only contains one parameter: the result (called @RETURN_VALUE). This last Stored Procedure gave (and still gives) me an error message telling me syntax error or access violation.

Hmm.. so perhaps it's the @RETURN_VALUE parameter, I thought. Using the Parameters collection editor of the BdpCommand, I removed the @RETURN_VALUE parameter from the list of parameters for each Stored Procedure, and guess what: the Stored Procedure dialog now gives me the expected results!
With the exception of the last one again (dso.Ten Most Expensive Products), since removing the parameter from that one only results in Delphi adding it again (by default).

So I learned two things:

  • SQL Server / MSDE Stored Procedures can be tested with the Stored Procedure dialog if we first remove the @RETURN_VALUE parameter (if any) from the Parameters list of the BdpCommand.
  • SQL Server / MSDE Stored Procedures that only return a result, but have no other parameters, cannot be executed from the Stored Procedure dialog, or using a BdpCommand (but using a SqlCommand they work fine).
As a workaround for the parameter-less Stored Procedures that return a result, I found out that adding a dummy parameter to their definition in SQL Server / MSDE is enough.
However, it's still a bug in the way BDP handles this, so I've filed a Quality Central report, number #15527.

Note that it's only a problem with SQL Server / MSDE. When using InterBase or DB2, you never get a @RETURN_VALUE parameter anyway.


1 Comment

Jayhawk 11/04/28 16:10:48Kewl you suohld come up with that. Excellent!

New Comment (max. 2048 characters, no HTML):


This webpage © 2005-2014 by Bob Swart (aka Dr.Bob - All Rights Reserved.