FIX: T-SQL Debugger Parses SQL Server 2000 Return Value Incorrectly (272074)

SYMPTOMS

To obtain parameter information for the stored procedure that is being debugged, the Visual Basic T-SQL Debugger runs the SQL Server system stored procedure sp_sproc_columns. On SQL Server 2000, sp_sproc_columns returns @RETURN_VALUE rather than RETURN_VALUE as the name of the stored procedure return value parameter. Because the T-SQL Debugger is looking for the literal name RETURN_VALUE, this change in behavior causes the Visual Basic T-SQL Debugger to treat the return value incorrectly as an input/output parameter; as a result, the stored procedure debugging fails.

When you click Step Into in the Visual Basic T-SQL Debugger window to debug a stored procedure, you may receive the following error message:

The following SQL Server error occurred. [Microsoft][ODBC SQL Server Driver][SQL Server] Procedure or function CustOrderHist has too many arguments specified.

After you click OK, the debugger raises the following error:

The query could not be debugged due to a problem coordinating events with the server.

RESOLUTION

To resolve this problem, download Visual Basic 6.0 Service Pack 5 (SP5).

WORKAROUND

To work around this problem, edit the incorrect call syntax in the Query box on the Stored Procedure tab. If you delete the "extra" first parameter, which is always displayed as zero (0), you can execute and debug the stored procedure as required. For example, change the call syntax from

{CALL CustOrderHist (0, ' ')}

to:

{CALL CustOrderHist (' ')}

IMPORTANT It is essential that you supply appropriate values for the other parameter(s) before you make the above-mentioned change; that is, editing the call syntax must be your last step before you click Execute. If you delete the "extra" first parameter, then select another parameter in the Parameter list, or if you type a value in the Value box to modify a parameter value, the "extra" parameter (0) is reinserted in the call syntax.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Service Pack 5 for Microsoft Visual Basic. For additional information about Visual Studio service packs, click the following article numbers to view the articles in the Microsoft Knowledge Base:

194022 INFO: Visual Studio 6.0 Service Packs, What, Where, Why

194295 HOWTO: Tell That a Visual Studio Service Pack Is Installed

To download the latest Visual Studio service pack, visit the following Microsoft Web site:

http://msdn.microsoft.com/vstudio/downloads/updates/

MORE INFORMATION

Steps to Reproduce Behavior

This problem only reproduces with Visual Basic Enterprise Edition 6.0, Service Pack 4 or earlier. In addition, you must be able to connect to an instance of SQL Server 2000.

If necessary, use the Visual Basic Add-In Manager to load the T-SQL Debugger add-in.
From the Visual Basic Add-Ins menu, run the T-SQL Debugger.
On the Settings tab, create a data source name (DSN) to the Northwind sample database on SQL Server 2000. Type any log on information that is required.
On the Stored Procedure tab, the first stored procedure, CustOrderHist, is selected by default. This stored procedure takes a single input parameter, @CustomerID, which should normally be the only entry in the Parameters list box. While waiting for the user to supply the input value, the Query box should normally display the following call syntax:
```
{CALL CustOrderHist (' ')}
						
```
However, because of this problem, @RETURN_VALUE is also listed in the Parameters list box, and the Query box displays the following call syntax:
```
{CALL CustOrderHist (0, ' ')}
						
```
This indicates that it has incorrectly interpreted the renamed return value as an input/output parameter.
Click Execute. In the Debugger window, click Step Into. You receive the above-mentioned error messages.