INF: Protocol Error in TDS Stream Causes Function Sequence Error from OLE Automation SPs

ID: Q243899

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

OLE Automation stored procedures need unrestricted access to the objects they want to invoke in order to function properly. Commonly, the following errors may be seen when unresolved issues with the target object persist:

[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
[Microsoft][ODBC SQL Server Driver]Function sequence error

MORE INFORMATION

When this happens, there may be several underlying issues occuring simultaneously. Primarily, the tabular data stream (TDS) error is for the most part a generic error, and unless you are also dealing with a Remote Data Service (RDS) implementation, this will not be a very clear pointer to the source of the issue. In order to augment the error information being returned, you need to replace the sp_oageterrorinfo call with a call to sp_displayoaerrorinfo. In order to access this additional information you first need to compile the two rather handy stored procedures mentioned in the following SQL Server 7.0 Books Online article:


   OLE Automation Return Codes and Error Information

The article also explains in detail why these stored procedures are needed in order to extract additional usable information, which is not discussed in this article.

When you have compiled the stored procedures, the preceding errors will give you much more usable information, as shown in the following code snippet that uses the modified error handling to give us much more interesting output:


DECLARE @object int
DECLARE @hr int

EXEC @hr = sp_OACreate 'Nic.cls', @object OUTPUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
--  EXEC sp_OAGetErrorInfo @object
    RETURN
END

What we get with the sp_displayoaerrorinfo call enabled in this instance looks like the following:

OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string

In this example we see that the "Invalid class string" error may indicate that the ProgID or CLSID (Nic.cls in preceding example) noted in the sp_oacreate call has not been successfully registered as an OLE object on the SQL Server. In our example, this is in fact the case because Nic.cls is a fictitious object.

For more information on these types of errors, take a look at the documentation for the sp_oaGetErrorInfo call in SQL Server Books Online.

In order to see a little more of what is going on here, add "Select @hr" in between the IF and the BEGIN statements in the code like so:


DECLARE @oFTP int
DECLARE @hr int  
DECLARE @vvar varchar(255)
DECLARE @vout int
EXEC @hr = sp_OACreate 'Nic.Inet', @object OUTPUT
SELECT @hr
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
--   EXEC sp_OAGetErrorInfo @object
    RETURN
END

This returns the following error information as enabled previously:


-----------
-2147221005

(1 row(s) affected)

OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string

It should be noted that it may be tempting to just add an @hr to the EXEC sp_OAGetErrorInfo @object call, which if tried will indeed return a false success in a "command completed successfully" result. However, this in fact is NOT what we want, because we need to know why we are getting a non-zero result for @hr in the code.

Additional query words: OLE Automation sp_oamethod sp_oacreate sp_displayoaerrorinfo "invalid class string"

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo