October 6, 2004 at 11:03 am
I am trying to pass an object as a parameter to another object while executing sp_OAMethod
Both the objects @objectExport and @object are created using sp_OACreate.
declare @Check1 INT
EXEC @hr = sp_OAMethod @objectExport, AddLedger, @Check1 Out , @pItem=@object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
PRINT @cHECK1
The function returns Boolean.
when I execute i get erro
0x80020005 ODSOLE Extended Procedure Type mismatch.
Need Help
===========================
DECLARE @objectExport int
declare @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'ExportToSAP.SAPExport', @objectExport OUT
--EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 1
-- Set a property.
EXEC @hr = sp_OASetProperty @objectExport, 'ExportFilePathName', 'C:\test.xml'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 2
-- Set a property.
EXEC @hr = sp_OASetProperty @objectExport, 'CompanyName', 'ENPRO INDUSTRIES PVT.LTD. [A-17]'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 3
-- create Ledger object
EXEC @hr = sp_OACreate 'ExportToSAP.Ledger', @object OUT
--EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 4
-- Set a property.
EXEC @hr = sp_OASetProperty @object, 'LedgerName', 'vijay'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
--set one more property
EXEC @hr = sp_OASetProperty @object, 'OpeningBalance ', '10000'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'X'
=================**********************
-- add ledger object to exportobject.. I get type missmatch problem in following code
declare @Check1 bit
EXEC @hr = sp_OAMethod @objectExport, AddLedger, @Check1 Out , @pItem = @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
PRINT @cHECK1
Print 'Y'
declare @Check varchar(20)
EXEC @hr = sp_OAMethod @objectExport, CreateExportFile, @Check Out , @pExportType = 1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
select @Check
EXEC @hr = sp_OADestroy @objectExport
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectExport, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
October 7, 2004 at 10:12 am
"Data type of a Transact-SQL local variable used to store a returned property value or a method return value did not match the Visual Basic data type of the property or method return value. Or, the return value of a property or a method was requested, but it does not return a value."
per BOL
So either that mean you can't pass an object
or it means the method called only does something, and doesn't return an object to work with
You probably need to check up on the props and methods of the object you're calling on and see whether its designed to do that.
October 12, 2004 at 2:56 pm
Not sure exactly what you are attempting, but maybe word <AddLedger> needs single quotes around it?
Jeff
October 13, 2004 at 2:43 am
As Jeff suggests:
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server',
'my_login', 'my_password'
is the example from BOL, and the second param, where you've got AddLedger, needs to be either a quoted string or a @variable
May 13, 2005 at 1:08 pm
I've got a similar problem that I can summarise quite easily.
I do both
EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @xmlHTTP OUT
and
EXEC @hr = sp_OACreate 'Microsoft.XMLDOM', @xmlDOM OUT
and set some properties, call the Open method on XMLHTTP, etc.
but the one thing I can't do that I need to be able to is
to get the XMLHTTP object to use the XMLDOM object,
i.e.
EXEC @hr = sp_OAMethod @xmlHTTP, 'send', NULL, @xmlDOM
This doesn't work, which is presumably because @xmlDOM isn't really
an object reference that can be passed, it's just a handle you
can use within the stored procedure.
I imagine there might be a way to get a reference that can be passed
in this way, but haven't been able to find anything.
Any thoughts people?
Cheers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply