SQL Server 2000 changes name of RETURN_VALUE to @RETURN_VALUE

View products that this article applies to.

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 (http://support.microsoft.com/kb/137636/ ) Relationship of the ODBC driver to INSTCAT.SQL

Back to the top

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.

Back to the top

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.

Back to the top

STATUS

This behavior is by design.

Back to the top

MORE INFORMATION

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=<UID> and Password=<strong password> to the correct values before you run this code. Make sure that <UID> 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 = "Provider=SQLOLEDB;Initial Catalog=Northwind;Data Source=(local);User ID=<UID>;Password=<strong password>;"
  cn.Open
  With cmd
    Set .ActiveConnection = cn
    .CommandText = "CustOrderHist"
    .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.

Back to the top