Microsoft KB Archive/285295

= SQL Server 2000 changes name of RETURN_VALUE to @RETURN_VALUE =

Article ID: 285295

Article Last Modified on 12/15/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.8
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q285295



SYMPTOMS
When working with parameterized SQL Server stored procedures by using a data access technology such as ActiveX Data Objects (ADO), the members of the Parameters collection of the ADO Command object can be referenced by using either the name of the parameter or its numeric index into the Parameters collection.

Every SQL Server stored procedure has a return value parameter (whether it is explicitly used or not) that is the first parameter (index of 0) in the Parameters collection of the ADO Command object. SQL Server 7.0 used RETURN_VALUE as the name of this parameter. However, SQL Server 2000 uses @RETURN_VALUE. This change may break existing applications that rely on the parameter name.

If you run the INSTCAT.SQL script included with MDAC 2.6, MDAC 2.7, or MDAC 2.8 against SQL Server 7.0, this script will change the parameter name to @RETURN_VALUE for this SQL Server 7.0 installation as well.

For additional information about the role of the INSTCAT.SQL script, click the following article number to view the article in the Microsoft Knowledge Base:

137636 Relationship of the ODBC driver to INSTCAT.SQL



CAUSE
This change in behavior is the result of changes to the system stored procedure, sp_params_rowset, in SQL Server 2000.

Note Users should not edit the system stored procedures in an attempt to rectify this problem.



RESOLUTION
As a result of this change, it is not possible to refer to the return value parameter by name in such a way that client application code works with both SQL Server 7.0 and SQL Server 2000.



STATUS
This behavior is by design.



Steps to reproduce the behavior
In order to see the name for the return value parameter that is expected by your version of SQL Server, place a Command Button on a form in a Visual Basic project and copy-and-paste the following code, adjusting your SQL Server connection parameters as necessary:

Note You must change User ID= and Password= to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Private Sub Command1_Click Dim cn As ADODB.Connection Dim cmd As ADODB.Command Set cn = New ADODB.Connection Set cmd = New ADODB.Command cn.ConnectionString = &quot;Provider=SQLOLEDB;Initial Catalog=Northwind;Data Source=(local);User ID=;Password= ;&quot; cn.Open With cmd Set .ActiveConnection = cn   .CommandText = &quot;CustOrderHist&quot; .CommandType = adCmdStoredProc End With cmd.Parameters.Refresh MsgBox cmd.Parameters(0).Name Set cmd = Nothing cn.Close Set cn = Nothing End Sub If you are using SQL Server 2000, the result is @RETURN_VALUE. If you are using SQL Server 7.0 or prior, the result is RETURN_VALUE.

Keywords: kbnofix kbstoredproc kbdatabase kbprb kbmdacnosweep KB285295

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.