BUG: Sp_OA Procedures May Produce "Bad Variable Type" Error

• Microsoft SQL Server, version 6.5

SYMPTOMS

When you attempt to call sp_OAMethod, you may receive either of the following errors:

   Error: 0x800200008   Bad variable type
   Error: 0x80020005    Type mismatch

If the datatype you are passing is not a string datatype, it cannot be passed by reference. Attempting to pass values other than string by reference results in the "Type mismatch" error.

WORKAROUND

To work around this problem, do any one of the following:

• Initialize your variable to a default value.
• Do not use NULL values.
• Use string data types wherever possible.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The following script produces the "Bad Variable Type" error.

   DECLARE @object     int
   DECLARE @oResultSet int
   DECLARE @hr         int
   DECLARE @strInfo    char(255)
   EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT
   EXEC sp_OAGetErrorInfo @object


   EXEC sp_OAMethod @object,"Connect", NULL, ".", "sa"
   EXEC sp_OAGetErrorInfo @object

   EXEC sp_OAMethod @object,
         "ExecuteWithResultsAndMessages",
         @oResultSet OUT,
         @Messages=@strInfo OUT,
         @Command="print 'Hello'"

   EXEC sp_OAGetErrorInfo @object

The following is a simple Visual Basic object method. It attempts to accept the integer value ByRef.

   Public Function DoIt(ByRef ID As Integer) As Integer
      DoIt = 1
      ID = 109
      End Function

   DECLARE @object    int
   DECLARE @hr        int

   DECLARE @iVal      int
   select @iVal = 1

   EXEC @hr = sp_OACreate MyTest.Class1', @object OUT
   EXEC sp_OAGetErrorInfo @object

   EXEC sp_OAMethod @object, "DoIt", NULL, @iVal OUTPUT
   EXEC sp_OAGetErrorInfo @object
   go