INFO: "Optional Feature Not Implemented" Error Message

Article translations Article translations
Article ID: 214459 - View products that this article applies to.
This article was previously published under Q214459
Expand all | Collapse all

SUMMARY

When you use ActiveX Data Objects (ADO) to pass parameters to a stored procedure, you may receive the following error:
Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional Feature Not Implemented.

MORE INFORMATION

This error can occur if you attempt to set the TYPE of a parameter in an ADODB command object's parameters collection to a type that is not supported by the data provider.

For example, using SQL Server 7.0, create a stored procedure on the PUBS database:
   CREATE PROCEDURE GetEmployeeInfo (@thedate datetime, @NumEmployees int output)AS 
       SELECT @NumEmployees =  count(*) FROM EMPLOYEE WHERE hire_date < @thedate
   GO
				
This stored procedure returns an output parameter of type int indicating the number of employees hired before a given date. The date is passed to the stored procedure as a parameter, and the number of employees is passed to the calling program as an output parameter.

Now create an ADO application to use the stored procedure. The example given is written in Visual Basic.
Private Sub MySubroutine()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command

Set dbConnection = New ADODB.Connection
Set dbCommand = New ADODB.Command

Dim DSNNAME As String
Dim USERNAME As String
Dim PASSWORD As String

DSNNAME = "Pubs"
USERNAME = "sa"
PASSWORD = ""

dbConnection.Open DSNNAME, USERNAME, PASSWORD
dbCommand.ActiveConnection = dbConnection

Dim TheDate As Date
TheDate = Now

dbCommand.CommandText = "GetEmployeeInfo"
dbCommand.CommandType = adCmdStoredProc
dbCommand.Parameters.Append dbCommand.CreateParameter("@thedate", adDBDate, adParamInput, 0, TheDate)
dbCommand.Parameters.Append dbCommand.CreateParameter("@NumEmployees", adInteger, adParamOutput, 0)
dbCommand.Execute

Dim strTheString As String
strTheString = "There are " & dbCommand.Parameters("@numemployees") & " employees who were hired before " & TheDate
MsgBox strTheString, vbOKOnly, "Demonstration"
End Sub
				
When the sample code is run, it gives this error:
Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional feature not Implemented.
This is because SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @theDate parameter to adDBTimeStamp.

In order to determine the number, names, types, and sizes of parameters needed in a stored procedure, use the Parameters.Refresh method of the command object. You can call this method during development of your application to determine the correct requirements for the stored procedure, then remove the expensive call to Parameters.Refresh after you have gathered the necessary data.

Properties

Article ID: 214459 - Last Review: August 23, 2001 - Revision: 1.0
APPLIES TO
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbinfo kbstoredproc KB214459
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com