Incorrect parameter information is returned when you call the GetParameterInfo method, and the command text is set to an ODBC call escape sequence (884437)


The information in this article applies to:

  • Microsoft Data Access Components 2.8

SYMPTOMS

When you use the GetParameterInfo method, and the command text is set to an Open Database Connectivity (ODBC) call escape sequence to obtain parameter information from a stored procedure, incorrect parameter information is returned. For example, when you set the command text to the following ODBC call escape sequence, the information that is returned is about the first parameter instead of about the last parameter.
{call yourStoredProcedure(,,,?)}
Note yourStoredProcedure is a placeholder for one of your stored procedures.

In this example, you are requesting information about the last parameter from the yourStoredProcedure stored procedure. However, information about the first parameter is returned instead.

CAUSE

This problem occurs because a problem in the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) client causes all the parameter information except for the first parameter to be discarded.

WORKAROUND

To work around this problem, use one of the following methods:
  • Use the Microsoft SQL Server ODBC driver. When you use the SQL Server ODBC driver, the GetParameterInfo method works as expected.
  • Use Transact-SQL as the command text. To do this, use the SetCommandText method to set the command text to code that is similar to the following code.
    exec ?=yourStoredProcedure ,,,,?

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

  1. Create the following stored procedure.
    CREATE procedure JDATATYPES1
(
@smi smallint = 2718 OUTPUT ,
@int integer  = 2718 OUTPUT ,
@tin tinyint  = 218  OUTPUT ,
@bin bigint   = 2718 OUTPUT ,
@bit bit      = 1    OUTPUT
)
as

select @smi;
select @int;
select @tin;
select @bin;
select @bit;

set @smi  = 3456 ;
set @int  = 3456 ;
set @tin  = 234 ;
set @bin  = 123456789012345;
set @bit  = 12    ;


return (125);
GO
  2. Start Rowset Viewer.

    Note Rowset Viewer is included with the Microsoft Data Access Components (MDAC) 2.8 Software Development Kit (SDK). To download the MDAC 2.8 SDK, visit the following Microsoft Web site:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=5067faf8-0db4-429a-b502-de4329c8c850&displaylang=en

  3. Connect to the data source where you created the stored procedure in step 1.
  4. In the Command window, type the following ODBC call escape sequence:

    {call JDATATYPES1(,,,?)}

  5. On the Command menu, click ICommandText, and then click SetCommandText.
  6. On the Command menu, click ICommandPrepare, and then click Prepare.
  7. On the Command menu, click ICommandWithParameters, and then click GetParameterInfo.

    Note Information about the first parameter is returned.
  8. In the Command window, type the following Transact-SQL script:

    exec ?=JDATATYPES1 ,,,,?

  9. Repeat steps 5 through 7.

    Note Information about the last parameter is returned.

REFERENCES

For more information, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledb_oledb_interfaces.asp

Modification Type:MajorLast Reviewed:4/13/2005
Keywords:KBSQLServ2005fix kbtshoot kbBug kbnofix kbprb KB884437 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.