Microsoft KB Archive/173848: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 12: | Line 12: | ||
<div id="TitleRow"> | <div id="TitleRow"> | ||
= <span id="KB173848"></span>BUG: Sp_OA Procedures May Produce | = <span id="KB173848"></span>BUG: Sp_OA Procedures May Produce "Bad Variable Type" or "Type Mismatch" Error = | ||
Line 65: | Line 65: | ||
Either of these messages can be returned if your variable has not been initialized or contains a value of NULL.<br /> | Either of these messages can be returned if your variable has not been initialized or contains a value of NULL.<br /> | ||
<br /> | <br /> | ||
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 | 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. | ||
</div> | </div> | ||
Line 91: | Line 91: | ||
== MORE INFORMATION == | == MORE INFORMATION == | ||
The following script produces the | 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.<br /> | ||
<pre class="codesample"> DECLARE @object int | <pre class="codesample"> DECLARE @object int | ||
Line 100: | Line 100: | ||
EXEC sp_OAGetErrorInfo @object | EXEC sp_OAGetErrorInfo @object | ||
EXEC sp_OAMethod @object, | EXEC sp_OAMethod @object,"Connect", NULL, ".", "sa" | ||
EXEC sp_OAGetErrorInfo @object | EXEC sp_OAGetErrorInfo @object | ||
EXEC sp_OAMethod @object, | EXEC sp_OAMethod @object, | ||
"ExecuteWithResultsAndMessages", | |||
@oResultSet OUT, | @oResultSet OUT, | ||
@Messages=@strInfo OUT, | @Messages=@strInfo OUT, | ||
@Command= | @Command="print 'Hello'" | ||
EXEC sp_OAGetErrorInfo @object | EXEC sp_OAGetErrorInfo @object | ||
</pre> | </pre> | ||
<br /> | <br /> | ||
If you initialize the @strInfo to | If you initialize the @strInfo to "", the problem is avoided.<br /> | ||
<br /> | <br /> | ||
The following is a simple Visual Basic object method. It attempts to accept the integer value ByRef. | The following is a simple Visual Basic object method. It attempts to accept the integer value ByRef. | ||
Line 120: | Line 120: | ||
End Function | End Function | ||
</pre> | </pre> | ||
The following script attempts to invoke the method but ends with the | The following script attempts to invoke the method but ends with the "Type mismatch" error. | ||
<pre class="codesample"> DECLARE @object int | <pre class="codesample"> DECLARE @object int | ||
DECLARE @hr int | DECLARE @hr int | ||
Line 130: | Line 130: | ||
EXEC sp_OAGetErrorInfo @object | EXEC sp_OAGetErrorInfo @object | ||
EXEC sp_OAMethod @object, | EXEC sp_OAMethod @object, "DoIt", NULL, @iVal OUTPUT | ||
EXEC sp_OAGetErrorInfo @object | EXEC sp_OAGetErrorInfo @object | ||
go | go |
Latest revision as of 11:07, 21 July 2020
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:
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