Microsoft KB Archive/218751

= FIX: ADO Command Incorrectly Returns Identity From Stored Procedure =

Article ID: 218751

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 1.5
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q218751



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.



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



STATUS
This problem was corrected in MDAC 2.6.



Steps to Reproduce Behavior
 Create a new Visual Basic Standard EXE project. Form1 is created by default. Place a CommandButton on the form1. Add a reference to the Microsoft ActiveX Data Objects Library.  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= ;DATABASE=pubs" 'cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ;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") '< 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.

Keywords: kbbug kbfix kbstoredproc kbdatabase kbmdacnosweep KB218751

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.