Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: ADO Does Not Support Default Values in Stored Procedures

PRB: ADO Does Not Support Default Values in Stored Procedures

View products that this article applies to.

This article was previously published under Q284942

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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

↑ Back to the top

Keywords: KB284942, kbstoredproc, kbprb

↑ Back to the top

Article Info
Article ID : 284942
Revision : 3
Created on : 5/12/2003
Published on : 5/12/2003
Exists online : False
Views : 352