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

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

ID článku: 243899 - Poslední kontrola: 10. 7. 2008 - Revize: 1

Váš názor