nathbalaka
asked on
sp_OAMethod
I have a problem running sp_OAMethod. My code looks like this..
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)
-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'Business.clsBusinessLogic
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', 'MV',50004
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Print the value returned from the method call
SELECT @retString
-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)
-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'Business.clsBusinessLogic
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', 'MV',50004
IF (@retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
-- Print the value returned from the method call
SELECT @retString
-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle
And your problem is ?
Please maintain your many old open questions. For the record:
1 09/05/2003 20 update image field Open Microsoft SQL Server
2 09/20/2003 50 error handling Open Microsoft SQL Server
3 12/10/2003 50 How to find user existence Open Databases
4 10/20/2003 40 call variable from one file to another f... Open Microsoft SQL Server
5 12/17/2003 50 date datatype Open Microsoft SQL Server
6 04/05/2004 125 add identity property Open Microsoft SQL Server
7 04/26/2004 100 sp_OAMethod Open Microsoft SQL Server
8 08/12/2003 30 Capture error number in procedure Open Microsoft SQL Server
9 08/26/2003 20 invalid connection Open Microsoft SQL Server
10 12/30/2003 250 Index space estimation Open Databases
1 09/05/2003 20 update image field Open Microsoft SQL Server
2 09/20/2003 50 error handling Open Microsoft SQL Server
3 12/10/2003 50 How to find user existence Open Databases
4 10/20/2003 40 call variable from one file to another f... Open Microsoft SQL Server
5 12/17/2003 50 date datatype Open Microsoft SQL Server
6 04/05/2004 125 add identity property Open Microsoft SQL Server
7 04/26/2004 100 sp_OAMethod Open Microsoft SQL Server
8 08/12/2003 30 Capture error number in procedure Open Microsoft SQL Server
9 08/26/2003 20 invalid connection Open Microsoft SQL Server
10 12/30/2003 250 Index space estimation Open Databases
missing positional parameter for return value
declare @return int
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', @return OUT, 'MV',50004
HTH
Hilaire
declare @return int
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', @return OUT, 'MV',50004
HTH
Hilaire
ASKER
The got some error description while executing sp_OAMethod
(Error Source - ODSOLE Extended Procedure,
Description - sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]])
I tried running the same method in an asp page and I got the error
(error 91 - Object Variable not set or with block variable not set)
(Error Source - ODSOLE Extended Procedure,
Description - sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]])
I tried running the same method in an asp page and I got the error
(error 91 - Object Variable not set or with block variable not set)
Sounds like some issue with the COM component then or a method name typo, it looks like the object is getting created but you are not able to call the method. No typo's in the Method name ? If you can get it to work in ASP, it should work with sp_OAMethod also. I would modify the method return to NULL instead of an INT declaration as Hilaire had if you do not know the return type from the method and do not care about processing the return in your stored proc --
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', NULL, 'MV',50004
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', NULL, 'MV',50004
I am sorry you have chosen to ignore my requests to close your open questions. I will check your profile in 7 days time; if you have not made substantial progress in closing OR updating your questions in that time, I will forward your account details to the Administrators for further action.
ASKER
Sorry that I am late in replying..
The method you are calling is not returning any value..so no need of any return type declarations. As you said if the method works fine in ASP then it should work fine with the sp_OAMethos too. I am trying different things but I am getting the same error
(error 91 - Object Variable not set or with block variable not set)
Perkins I am not ignoring your requests..I will get the thing done as soon as I am finished with this important with my client. Really very important thing this for me.
The method you are calling is not returning any value..so no need of any return type declarations. As you said if the method works fine in ASP then it should work fine with the sp_OAMethos too. I am trying different things but I am getting the same error
(error 91 - Object Variable not set or with block variable not set)
Perkins I am not ignoring your requests..I will get the thing done as soon as I am finished with this important with my client. Really very important thing this for me.
You use positional parameters (as opposed to named parameters).
So you can't omit optional parameter at position 3 if you give method parameters on further positions
You must provide either
- a return variable
- a null placeholder
- an implicit null (two commas with nothing in between)
From the error message posted above, it sounds like the sp_OAcreate failed to create a valid object
Did you check that the ActiveX / COM object is registered on your SQL Server
To register a server ActiveX,
copy the dll on the SQL Server
start-->execute
regsvr32 path\dllname
HTH
Hilaire
So you can't omit optional parameter at position 3 if you give method parameters on further positions
You must provide either
- a return variable
- a null placeholder
- an implicit null (two commas with nothing in between)
From the error message posted above, it sounds like the sp_OAcreate failed to create a valid object
Did you check that the ActiveX / COM object is registered on your SQL Server
To register a server ActiveX,
copy the dll on the SQL Server
start-->execute
regsvr32 path\dllname
HTH
Hilaire
After using the syntax Hilaire had suggested (or using NULL for return param), what is the EXACT error ?
ASKER
The ActiveX/COM object was registered successfully when I used regsvr32... The latest news is that I am able to run the code in the ASP page. The problem was setting up the connection (Object.SetConnection 'CONNECTION STRING') After that I called the 'Qualify' method and It worked fine.
I dont know how to set connection in SQL Server.
I dont know how to set connection in SQL Server.
Post the ASP code that works and we'll try to transalte
If in ASP you need to issue
Object.SetConnection 'CONNECTION STRING'
chances are that you have to do the same in SQL Server
EXEC @retVal = sp_OAMethod @comHandle, 'SetConnection', null, 'CONNECTION STRING'
Object.SetConnection 'CONNECTION STRING'
chances are that you have to do the same in SQL Server
EXEC @retVal = sp_OAMethod @comHandle, 'SetConnection', null, 'CONNECTION STRING'
If in ASP, you used
Object.SetConnection = "CONNECTION STRING"
This will translate to
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', 'CONNECTION STRING'
Object.SetConnection = "CONNECTION STRING"
This will translate to
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', 'CONNECTION STRING'
Oops, Yes
You're absolutely right
Thanks for correcting me jchopde
Hilaire
You're absolutely right
Thanks for correcting me jchopde
Hilaire
ASKER
I have used
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', 'CONNECTION STRING'
It says 'invalid number of parameters'
I have even tried using 'NULL' as follows because there is no return value.
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', NULL, 'CONNECTION STRING'
But the error is the same
The SetConnection worked fine in ASP but dont know why this error in SQL Server.
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', 'CONNECTION STRING'
It says 'invalid number of parameters'
I have even tried using 'NULL' as follows because there is no return value.
EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', NULL, 'CONNECTION STRING'
But the error is the same
The SetConnection worked fine in ASP but dont know why this error in SQL Server.
Again, If you have working ASP code, post it and we'll translate
Agree with Hilaire, need to see working ASP code to help out any further.
ASKER
Hey experts..I am back and with the solution. The code worked fine when I used the sp_OAMehtod like this.
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify("MV","12345")'
I have another classic problem..I have to invoke a DLL on an other machine. Thats means database on one machine and the application on the other machine..Please help me how can I do that???
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify("MV","12345")'
I have another classic problem..I have to invoke a DLL on an other machine. Thats means database on one machine and the application on the other machine..Please help me how can I do that???
ASKER
Is it possible to invoke a DLL on another machine through SQL Server??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.