Knowledge Base

PRB: ADO Does Not Support Default Values in Stored Procedures

Article ID: 284942

Article Last Modified on 5/12/2003

APPLIES TO

Microsoft Data Access Components 2.6, when used with:
- Microsoft SQL Server 7.0 Standard Edition
Microsoft Data Access Components 2.7, when used with:
- Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q284942

SYMPTOMS

Calling a prepared stored procedure that has parameters with default values from ActiveX Data Objects (ADO) may return the following error message if all parameters are not bound:

Run-time error '-2147217900 (80050e14)':
Prepared statement '(@P1 int OUTPUT, @P2 nvarchar(15), @P3 nvarchar(4)) EXEC @P1=Sales' expects parameter @P3, which was not supplied.

CAUSE

The error is reported by SQL Server 7.0. ADO fetches the metadata for the parameters and prepares a stored procedure. ADO is not able to determine if any of the parameters have default values because this information is not available from SQL Server. When the parameter data is not supplied, ADO then calls the prepared call to the stored procedure without the data for the parameter, which generates the error.

RESOLUTION

To work around this error, do not use the Prepare method when calling a stored procedure on SQL Server 7.0.

Using Prepare with a stored procedure is redundant, because a stored procedure on SQL Server is already stored with its execution plan and is prepared when it is originally created.

STATUS

This behavior is by design.

MORE INFORMATION

To reproduce this error, create a Microsoft Visual Basic (VB) or Active Server Pages (ASP) application and use the ADO code below to connect to SQL Server 7.0 and run the SalesByCategory stored procedure in the Northwind sample database.

The SalesByCategory stored procedure has 2 parameters defined: @CategoryName and @OrdYear. The @OrdYear paramet has a default value of '1998'.

If the .Prepared = True is removed, this code works correctly and does not return an error. If this code is used with SQL Server 2000, the code works correctly due to a new feature which defers the prepare of calls until execution.

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection

cn.Provider = "SQLOLEDB"
cn.ConnectionString = "server=MySQL70;database=Northwind;uid=sa;pwd=;"
cn.Open

With cmd
  Set .ActiveConnection = cn
  .CommandText = "SalesByCategory"
  .CommandType = adCmdStoredProc
  .Prepared = True
End With

cmd.Parameters.Refresh
cmd.Parameters("@CategoryName").Value = "Beverages"

Set rs = cmd.Execute

rs.Close
cn.Close

Additional query words: SQLOLEDB;ADO;VB;MDAC

Keywords: kbstoredproc kbprb KB284942

Microsoft KB Archive/284942

Contents