Microsoft KB Archive/243899

= How to access additional information about errors that are returned when you use the OLE Automation system stored procedures =

Article ID: 243899

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q243899



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.

Note If you are using Microsoft SQL Server 2005, you may have to slightly change this code. For more information, see the "OLE Automation return codes and error information" topic in SQL Server 2005 Books Online.

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

Keywords: kbinfo KB243899

-

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

© Microsoft Corporation. All rights reserved.