Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/173848

From BetaArchive Wiki
Knowledge Base


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

Article ID: 173848

Article Last Modified on 2/14/2005



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition



This article was previously published under Q173848

BUG #: 17172 (SQLBUG_65)
       36455 (SQLBUG_70)
       36455 (SHILOH)
        

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


Either of these messages can be returned if your variable has not been initialized or contains a value of NULL.

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 that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The following script produces the "Bad Variable Type" error. Use 'SQLDMO.SQLServer' for SQL Server 7.0 instead of 'SQLOLE.SQLServer' for SQL Server 6.5 as shown in script.

      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
                


If you initialize the @strInfo to "", the problem is avoided.

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
                

The following script attempts to invoke the method but ends with the "Type mismatch" error.

      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
                


Additional query words: data type VB ole automation

Keywords: kbbug kbcode KB173848