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

MORE INFORMATION

Steps to Reproduce Behavior

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

2. Start Visual Basic 5.0 Enterprise Edition.
3. Create a Standard EXE project.
4. Add a reference to Microsoft ActiveX Data Objects 1.5.
5. Enter the following code in the Load method of the default form (make sure that the correct permissions are applied):
   

         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=UserName;pwd=StrongPassword;"
            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.: " & Err.Number & vbLf & _
               "Error: " & Err.Description
            Set oRs = Nothing
   
         End Sub
   						

6. 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=UserName;pwd=StrongPassword;"
      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