PRB: ADO Command Incorrectly Returns Identity From Stored Proc

• Microsoft Data Access Components versions 1.5, 2.0, 2.1

SYMPTOMS

When calling a stored procedure that excepts a TEXT type as an input argument and has an output argument that returns an identity field, if the TEXT argument is greater than 8000 characters then the output parameter does not return the identity correctly (instead it returns random values). This problem only occurs when using the MSDASQL OLE DB provider.

RESOLUTION

Currently, the only workaround to this problem is to use the SQLOLEDB provider.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE project. Form1 is created by default. Place a CommandButton on the form1.



2. Add a reference to the Microsoft ActiveX Data Objects Library.



3. Place the following code behind the CommandButton's Click event:
   
   NOTE: You will need to change the connection string to connect to your SQL Server.




    Dim cmd As ADODB.Command
    Dim cn As ADODB.Connection
    Dim cmdText As String
    Dim SQL As String
    
    'Get connection ... you will need to modify the connect string
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "MSDASQL.1;Persist Security Info=False;Extended Properties=Driver={SQL Server};SERVER=YourServer;UID=sa;DATABASE=pubs"
    'cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=YourServer"
    
    'Create the Table and stored procedure for this test
    On Error Resume Next
    SQL = "create table QuickTest (" & _
            " c1 int identity, " & _
            " c2 TEXT  )"
    cn.Execute SQL
    
    SQL = " create proc QuickTest_proc @tvar TEXT, @iret int output " & _
            "as " & _
            "insert QuickTest values (@tvar) " & _
            "select @iret = @@identity "
    cn.Execute SQL
       
    On Error GoTo errhandler
    'Execute the stored procedure
    Set cmd = CreateObject("ADODB.Command")
    cmdText = "{?=call QuickTest_proc ( ?, ?) }"
    With cmd
        Set .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = cmdText
        .Parameters.Refresh
        .Parameters(0).Direction = adParamReturnValue
        .Parameters(1).Direction = adParamInput
        .Parameters(1).Value = String(8001, "x") '<<this fails w/ MSDASQL provider
        '.Parameters(1).Value = String(8000, "x") '<<this works w/ both providers
        .Parameters(2).Direction = adParamOutput
        .Execute
    End With
    Debug.Print "Cmd.Parameters(2).Value: " & cmd.Parameters(2).Value
    Exit Sub

errhandler:
    Debug.Print "Error: "; Err.Number & " -- " & Err.Description
 

4. Run the code. Note that the identity value is not returned correctly if the input parameter is greater than 8000 characters and you are using the MSDASQL provider. If you change to the SQLOLEDB provider, everything works as expected.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Mike Ruthruff, Microsoft Corporation

Additional query words:

Keywords : kbADO150bug kbADO200bug kbADO201bug kbStoredProc kbGrpVBDB
Version : WINDOWS:1.5,2.0,2.1
Platform : WINDOWS
Issue type : kbprb