Microsoft KB Archive/284942

= 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 = &quot;SQLOLEDB&quot; cn.ConnectionString = &quot;server=MySQL70;database=Northwind;uid=sa;pwd=;&quot; cn.Open

With cmd Set .ActiveConnection = cn .CommandText = &quot;SalesByCategory&quot; .CommandType = adCmdStoredProc .Prepared = True End With

cmd.Parameters.Refresh cmd.Parameters(&quot;@CategoryName&quot;).Value = &quot;Beverages&quot;

Set rs = cmd.Execute

rs.Close cn.Close

Additional query words: SQLOLEDB;ADO;VB;MDAC

Keywords: kbstoredproc kbprb KB284942

-

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

© Microsoft Corporation. All rights reserved.