Microsoft KB Archive/188558

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

Article ID: 188558

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.0
 * Microsoft ODBC Driver for Microsoft SQL Server 3.5
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft OLE DB 1.5

-



This article was previously published under Q188558



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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



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 (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  Save and run the form.</li></ol>

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

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

Keywords: kbbug kbfix kbstoredproc kbdatabase KB188558

-

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

© Microsoft Corporation. All rights reserved.