PRB: Error -2147217887 "No Error Description" When You Send SQL Text Field to Output Stream

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

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

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.

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

Steps to Reproduce Behavior

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

2. Create the Visual Basic test project as follows:
   1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
   2. From the Project menu, click References, and then click Microsoft ActiveX Data Objects 2.x Library.
   3. Add two CommandButton controls to Form1. Command1 and Command2 are created by default.
   4. 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 "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Northwind;" & _
      "User Id=user;Password=password;"
      
      cn.Execute "INSERT INTO TextTest(TextField) VALUES('" & String(1025, "x") & "')"
      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 "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=Northwind;" & _
      "User Id=user;Password=password;"
      
      Set cmd = New ADODB.Command
      Set cmd.ActiveConnection = cn
      cmd.CommandType = adCmdText
      cmd.CommandText = "SELECT TextField FROM TextTest WHERE id=1"
      
      Set strm = New ADODB.Stream
      strm.Open
      cmd.Properties("Output Stream") = strm
      
      'Uncomment the following line of code to resolve this problem:
      'cn.Execute "SET TEXTSIZE 200000"
      
      cmd.Execute , , adExecuteStream
      MsgBox strm.ReadText
      
      End Sub
      						

3. Run the test as follows:
   1. Press the F5 key to run the test project.
   2. Click Command1 to insert a single record into the test table.
   3. Click Command2 to retrieve the Text field from the first record in the test table. Notice that you receive the above-mentioned error message.

Workaround

1. Continue from the preceding steps, and return to Design mode in Visual Basic.
2. Uncomment the following line of code:

   'cn.Execute "SET TEXTSIZE 200000"

3. Press F5 to run the test project.
4. Click Command2 to retrieve the Text field. No error is returned, and you receive the correct output.