FIX: ADO Command Incorrectly Returns Identity From Stored Procedure

When you call a stored procedure that accepts 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, the output parameter does not return the identity correctly (instead it returns random values). This problem only occurs when you use the MSDASQL OLE DB provider.

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

This problem was corrected in MDAC 2.6.

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 must 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=<username>;DATABASE=pubs"
       'cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username>;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.