Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

FIX: ADO Command Incorrectly Returns Identity From Stored Procedure


View products that this article applies to.

This article was previously published under Q218751

↑ Back to the top


Symptoms

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.

↑ Back to the top


Resolution

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

↑ Back to the top


Status

This problem was corrected in MDAC 2.6.

↑ Back to the top


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 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.

↑ Back to the top


Keywords: KB218751, kbstoredproc, kbmdacnosweep, kbfix, kbdatabase, kbbug

↑ Back to the top

Article Info
Article ID : 218751
Revision : 4
Created on : 12/3/2003
Published on : 12/3/2003
Exists online : False
Views : 426