Solved

sp_OAMethod

Posted on 2004-04-26
22
2,691 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:nathbalaka
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 6

Expert Comment

by:jchopde
ID: 10918757
And your problem is ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10918880
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 10918907
missing positional parameter for return value

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

HTH

Hilaire
0
 

Author Comment

by:nathbalaka
ID: 10919792
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
 
LVL 6

Expert Comment

by:jchopde
ID: 10919918
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10924484
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
 

Author Comment

by:nathbalaka
ID: 10926681
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 10926776
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
 
LVL 6

Expert Comment

by:jchopde
ID: 10927489
After using the syntax Hilaire had suggested (or using NULL for return param), what is the EXACT error ?
0
 

Author Comment

by:nathbalaka
ID: 10927504
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Expert Comment

by:Hilaire
ID: 10927516
Post the ASP code that works and we'll try to transalte
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10927529
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
 
LVL 6

Expert Comment

by:jchopde
ID: 10927549
If in ASP, you used

Object.SetConnection = "CONNECTION STRING"

This will translate to

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

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10927587
Oops, Yes
You're absolutely right
Thanks for correcting me jchopde

Hilaire
0
 

Author Comment

by:nathbalaka
ID: 10927832
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 10927935
Again, If you have working ASP code, post it and we'll translate
0
 
LVL 6

Expert Comment

by:jchopde
ID: 10928268
Agree with Hilaire, need to see working ASP code to help out any further.
0
 

Author Comment

by:nathbalaka
ID: 10937546
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
 

Author Comment

by:nathbalaka
ID: 10937607
Is it possible to invoke a DLL on another machine through SQL Server??
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 100 total points
ID: 10937685
>>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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now