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

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: 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)
				

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.