FIX: Stored Procedure Invocation Returns "Protocol Error in TDS"

ID: Q188558

The information in this article applies to:

Microsoft SQL Server version 6.5
Microsoft ODBC Driver for SQL Server, versions 3.0, 3.5
ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
Microsoft OLE DB, version 1.5

BUG #: 22058 (MDAC)

SYMPTOMS

Calling a stored procedure on Microsoft SQL Server 6.5 that uses temporary tables may generate the following error under ActiveX Data Objects (ADO):

Run-time error -2147467259 (80004005)
Protocol error in TDS Stream

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

Steps to Reproduce Behavior

Create the following stored procedure on SQL Server 6.5:


      CREATE PROCEDURE tds_sp AS

      CREATE table #test(col1 varchar(10), col2 int)
      INSERT INTO #test values('test', 1)
      SELECT * FROM #test
      DROP TABLE #test
      GO

Start Visual Basic 5.0 Enterprise Edition.

Create a Standard EXE project.

Add a reference to Microsoft ActiveX Data Objects 1.5.

Enter the following code in the Load method of the default form:


      Private Sub Command1_Click()

         Dim oRs As New ADODB.Recordset
         Dim sCn As String
         Dim sExecute As String

         On Error GoTo errorhandler

         sCn = "DSN=pubs;uid=sa;pwd=;"
         sExecute = "tds_sp"

         oRs.CursorLocation = adUseClient
         oRs.Open sExecute, sCn, adOpenStatic, adLockBatchOptimistic

         While Not oRs.EOF
            Debug.Print oRs.Fields(1).Value
            oRs.MoveNext
         Wend

         Set oRs = Nothing
         Exit Sub

         errorhandler:

         MsgBox "Error No.: " &amp; Err.Number & vbLf & _
            "Error: " & Err.Description
         Set oRs = Nothing

      End Sub

Save and run the form.

One way to solve this problem is to use the following code, which gives a read only recordset. Note that this workaround is not necessary if SQL 6.5 Service Pack 5a or later has been installed.


   Dim oRs As New ADODB.Recordset
   Dim oCn As New ADODB.Connection
   Dim oCm As New ADODB.Command
   Dim sCn As String
   Dim sExecute As String

   On Error GoTo errorhandler

      sCn = "DSN=pubs;uid=sa;pwd=;"
      sExecute = "tds_sp"
      oCn.Open sCn
      oCn.CursorLocation = adUseClient
      oCm.ActiveConnection = oCn
      oCm.CommandType = adCmdStoredProc
      oCm.CommandText = "tds_sp"
      'Set oRs = oCm.Execute
      oRs.Open oCm, , adOpenStatic, adLockBatchOptimistic
      'oRs.CursorLocation = adUseClient
      'oRs.Open sExecute, sCn, adOpenStatic, adLockBatchOptimistic

      While Not oRs.EOF
         Debug.Print oRs.Fields(0).Value
         oRs.MoveNext
      Wend

      Set oRs = Nothing
      Exit Sub

     errorhandler:

     MsgBox "Error No.: " & Err.Number & vbLf & _
         "Error: " & Err.Description
     Set oRs = Nothing

Additional query words: adobj kbado TDS protocol error kbDSupport kbdse kbSQLServ

Keywords : kbADO150 kbADO200 kbDatabase kbStoredProc kbbug6.50 kbGrpVBDB kbGrpMDAC kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2,3.0,3.5; winnt:6.5
Platform : WINDOWS winnt
Issue type : kbbug