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', @comHandle OUTPUT
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
nathbalakaAsked:
Who is Participating?
 
HilaireConnect With a Mentor Commented:
>>EXEC @retVal = sp_OAMethod @comHandle, 'Qualify("MV","12345")' <<
So you were passing a parameter with wrong datatype
it should have been

EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', NULL, 'MV', '50004'

I guess we would have spotted it if you had provided decent feedback on this thread
(eg post the working ASP code)

>>Is it possible to invoke a DLL on another machine through SQL Server??<<
This is another question.
EE has a knowledge base, question title should help others to find a solution to their problems.
Two questions inside one is an infringement to EE rules.

Anyway I'll stop answering your questions until you stop ignoring the requests to maintain your open questions

Regards

Hilaire
0
 
jchopdeCommented:
And your problem is ?
0
 
Anthony PerkinsCommented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HilaireCommented:
missing positional parameter for return value

declare @return int
EXEC @retVal = sp_OAMethod @comHandle, 'Qualify', @return OUT, 'MV',50004

HTH

Hilaire
0
 
nathbalakaAuthor Commented:
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)
0
 
jchopdeCommented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
nathbalakaAuthor Commented:
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.
0
 
HilaireCommented:
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
0
 
jchopdeCommented:
After using the syntax Hilaire had suggested (or using NULL for return param), what is the EXACT error ?
0
 
nathbalakaAuthor Commented:
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.
0
 
HilaireCommented:
Post the ASP code that works and we'll try to transalte
0
 
HilaireCommented:
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'
0
 
jchopdeCommented:
If in ASP, you used

Object.SetConnection = "CONNECTION STRING"

This will translate to

EXEC @retVal = sp_OASetProperty @comHandle, 'SetConnection', 'CONNECTION STRING'

0
 
HilaireCommented:
Oops, Yes
You're absolutely right
Thanks for correcting me jchopde

Hilaire
0
 
nathbalakaAuthor Commented:
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.
0
 
HilaireCommented:
Again, If you have working ASP code, post it and we'll translate
0
 
jchopdeCommented:
Agree with Hilaire, need to see working ASP code to help out any further.
0
 
nathbalakaAuthor Commented:
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???
0
 
nathbalakaAuthor Commented:
Is it possible to invoke a DLL on another machine through SQL Server??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.