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

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

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