Microsoft KB Archive/297502

= PRB: Error -2147217887 &quot;No Error Description&quot; When You Send SQL Text Field to Output Stream =

Article ID: 297502

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q297502



SYMPTOMS
When you use the Execute method of an ActiveX Data Objects (ADO) Command object with an option of adExecuteStream, if the CommandText of the Command object that selects a SQL Server Text field is longer than 1,024 characters, you receive the following error message:

Run-time error '-2147217887(80040e21): No error description provided

No error is returned if there are 1,024 or fewer characters in the Text field. In addition, no error is returned if the field type is NText (which is tested up to 1,025,000 characters).



CAUSE
By default, the TEXTSIZE on the SQL Server side is set to 1,024 for the Output Stream.



RESOLUTION
There are two ways to resolve this problem:
 * Use the Connection object that serves as the Command object's ActiveConnection to set the TEXTSIZE to a value that is high enough to accommodate the long Text field. You must set the TEXTSIZE before you call the Execute method of the Command object.
 * Use a SQL NText field instead of a Text field.



MORE INFORMATION
The SQL Server SET TEXTSIZE statement specifies the maximum length, in bytes, of Text data that a SELECT statement returns.

Steps to Reproduce Behavior
  To create the SQL Server test table, run the following Action Queries in SQL Server Query Analyzer: USE NORTHWIND GO     CREATE TABLE TextTest ( [id] [INT] IDENTITY (1, 1) NOT NULL,          [TextField][TEXT] NULL) GO     ALTER TABLE TextTest WITH NOCHECK ADD CONSTRAINT [PK_TextTest] PRIMARY KEY NONCLUSTERED ([id]) GO                     Create the Visual Basic test project as follows:  Create a new Standard EXE project in Visual Basic. Form1 is created by default. From the Project menu, click References, and then click Microsoft ActiveX Data Objects 2.x Library. Add two CommandButton controls to Form1. Command1 and Command2 are created by default.  Paste the following code into the code window of Form1: Private Sub Command1_Click

Dim cn As ADODB.Connection Set cn = New ADODB.Connection

'Modify the connection string for your server, user, and password. cn.Open &quot;Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Northwind;&quot; & _ &quot;User Id=user;Password=password;&quot;

cn.Execute &quot;INSERT INTO TextTest(TextField) VALUES('&quot; & String(1025, &quot;x&quot;) & &quot;')&quot; End Sub

Private Sub Command2_Click

Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim strm As ADODB.Stream

Set cn = New ADODB.Connection

'Modify the connection string for your server, user, and password. cn.Open &quot;Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Northwind;&quot; & _ &quot;User Id=user;Password=password;&quot;

Set cmd = New ADODB.Command Set cmd.ActiveConnection = cn cmd.CommandType = adCmdText cmd.CommandText = &quot;SELECT TextField FROM TextTest WHERE id=1&quot;

Set strm = New ADODB.Stream strm.Open cmd.Properties(&quot;Output Stream&quot;) = strm

'Uncomment the following line of code to resolve this problem: 'cn.Execute &quot;SET TEXTSIZE 200000&quot;

cmd.Execute, , adExecuteStream MsgBox strm.ReadText

End Sub </li></ol> </li> Run the test as follows: <ol style="list-style-type: lower-alpha;"> Press the F5 key to run the test project.</li> Click Command1 to insert a single record into the test table.</li> Click Command2 to retrieve the Text field from the first record in the test table. Notice that you receive the above-mentioned error message.</li></ol> </li></ol>

Workaround
<ol> Continue from the preceding steps, and return to Design mode in Visual Basic.</li>  Uncomment the following line of code: 'cn.Execute &quot;SET TEXTSIZE 200000&quot; </li> Press F5 to run the test project.</li> Click Command2 to retrieve the Text field. No error is returned, and you receive the correct output.</li></ol>

Additional query words: -2147217887(80040e21)

Keywords: kbdatabase kbprb KB297502

-

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

© Microsoft Corporation. All rights reserved.