SP_OA
Jun 20, 2002
I am getting errors when i do sp_OA*
"Error Occurred Calling Object: ODSOLE Extended Procedure
sp_OADestroy usage: ObjPointerToBeDestroyed int IN."
This is what I did
Step1
'created a DLL for the following function
Public FirstNumber As Variant
Public SecondNumber As Variant
Function AdditionFunction(FirstNumber, SecondNumber)
AdditionFunction = FirstNumber + SecondNumber
Print AdditionFunction
End Function
Step2
Registered the Dll
regsvr32 d:mydlladditionfunction.dll
Step3
'Execute this script
DECLARE @cInputValue1 int
DECLARE @cInputValue2 int
DECLARE @cOutputValue int
DECLARE @objDLL int
declare @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)
SET @cInputValue1 = 5
SET @cInputValue2 = 6
EXECUTE @Hresult =sp_OACreate "additionFunction", @objDLL OUTPUT
-- objDLL holds a handle to your DLL
EXECUTE @Hresult =sp_OAMethod @objDLL, "additionfunction",NULL,@cInputValue1 ,@cInputValue2, @cOutputValue OUTPUT
print @cOutputValue
-- Don't forget to release it when you have finished
EXECUTE @Hresult =sp_OADestroy @objDLL
print @objDLL
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objDLL , @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END
View 1 Replies
Jul 23, 2005
I'm faced with a situation where I will need to calculate a column fora resultset by calling a component written as a VB6 DLL, passingparameters from the resultset to the component and setting (orupdating) a column with the result. I thought that perhaps the bestway out would be to create a UDF that passes the parameters to the VBcomponent using the sp_oa* OLE stored procs.For a test, I created an ActiveX DLL in VB6 (TestDLL) with someproperties and methods. I then created a function that creates theobject, sets the required properties and returns a result. I usesp_oaDestroy at the end of the function to remove the objectreference. The function seems to work surprisingly well except for asmall problem; when I use the function to calculate a column for aresultset with more that one row, the DLL appears to stay locked up("the file is being used by another person or program"). This leavesme with the impression that the object reference is not beingdestroyed. I have to stop/restart the SQL Server in order to free theDLL.Question:Is the UDF approach the best way? I don't like the idea of creatingand destroying the object at every pass which is what the UDF does.As an alternative, I suppose that I could have a single SP where Icreate the OLE object once, loop through the result set with a cursorand do my processing/updating, then close the OLE object. I must saythat I'm not too fond of that approach either.Thanks for your help,Bill E.Hollywood, FL(code is below)___________________________--Test the functionCreate Table #TestTable(Field1 int)INSERT INTO #TestTable VALUES (1)INSERT INTO #TestTable VALUES (2)SELECT Field1, dbo.fnTest(Field1,4) AS CalcColFROM #TestTableDrop Table #TestTable___________________________CREATE FUNCTION dbo.fnTest/*This function calls a VB DLL*/(--input variables@intValue1 smallint,@intValue2 smallint)RETURNS integerASBEGIN--Define the return variable and the counterDeclare @intReturnValue smallintSet @intReturnValue=0--Define other variablesDeclare @intObject intDeclare @intResult intDeclare @intError intSet @intError=0If @intError = 0exec @intError=sp_oaCreate 'TestDLL.Convert', @intObject OUTPUTIf @intError = 0exec @intError = sp_OASetProperty @intObject,'Input1', @intValue1If @intError = 0exec @intError = sp_OASetProperty @intObject,'Input2', @intValue2If @intError = 0exec @intError = sp_oamethod @intObject, 'Multiply'If @intError = 0exec @intError = sp_oagetproperty @intObject,'Output',@intReturnValue OutputIf @intError = 0exec @intError = sp_oadestroy @intObjectRETURN @intReturnValueEND
View 8 Replies
View Related
Mar 24, 2008
Okay, so here's my dilemma: I'm trying to figure out a way I can get a list of drives, users, groups, etc. from computers on the network. There are a couple of caveats:
1) This has to be done entirely using T-SQL. There can be no external components that need to be installed.
2) xp_cmdshell can NOT be used, both for security reasons and because some of the computers being polled do not have SQL Server installed.
I would like to use the sp_OA* stored procedures. So far I have been able to connect to a remote server and find the running state of SQL Server; however, when it comes to enumerating collections I'm kinda lost.
This has also been posted in the MS-SQL general forum.
Any help would be greatly appreciated!
View 1 Replies
View Related
Aug 20, 2007
i have sql2005 enterprise edition 64 bit edition and immediately after applying service pack 2 we are encountering few errors which are affecting our production very much
1. there is a user defined function which in turn calls sp_OA ....(ole automation procedures - this is enabled in the configuration manager) and it is working fine ....however over a period of time after 1-2 hours the functions returns unexpected values,
and when you restart sql server, the function returns normal values as expected
does service pack 2 upgrade in any way affect sp_OA extended system procedures..
2.i am getting this error contimously in my sql server log
Message
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'
database mail stops workign after some time and when you restart sql server it starts working
can somebody throw some light on this as it is very badly affecting my production
thanks in advance
Samuel
View 2 Replies
View Related
Jul 20, 2005
Hello to all,Maybe first small introduction:- SQLServer 2000 SP3,- XP Pro EN,- ActiveX,- SP in databaseIt should working like this.There is a instanse of an object working, which recieves "telegramms"from all clients, including SQLServer.In SP I set the special properities (see code below) and it works.Also works the method, which I call by this object.The only thing which is not working is, that I cannnot read the objectproperty, which I try to read!I was trying already, to do something that the SQL server wantblocking the object, but it is not the case.Below the code:---------------------------------------declare @iRetValintdeclare @iObjectintdeclare @sPropertyvarchar(2560)declare @sSource varchar(1000)declare @sDescription varchar(1000)declare @sLog varchar(1000)declare @dDateEVT datetimedeclare @sText1varchar(10)declare @sText2varchar(10)declare @iProperty intdeclare @nMessageNrnumericdeclare @bstrDateTime varchar(100)declare @textFromA1varchar(100)declare @textFromA2varchar(100)declare @i intset @iObject = 0set @dDateEVT = getdate()set @iRetVal = 0set @sText1 = 'AA00000000'set @sText2 = 'BB00000000'set @iProperty = 7set @i = 0-- {034188F2-8DBC-4613-829A-76D5279C35A3}exec @iRetVal = sp_OACreate 'RAIDSSimComponents.pidMessenger',@iObject OUTPUT,1IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: No object created. Source: ' + @sSource + 'Description: ' + @sDescriptionprint @sLogend-- Set the object propertyexec @iRetVal = sp_OASetProperty @iObject, 'FollowFromB', 1IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromA'print @sLogendexec @iRetVal = sp_OASetProperty @iObject, 'FollowFromB_EventID', 555IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromB'print @sLogend-- Call methodexec @iRetVal = sp_OAMethod @iObject,'SendNotification_FromA',@iProperty OUT, 555, @dDateEVT, @sText1, @sText2IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromA'print @sLogendset @sProperty = '?'set @i = 1-- Start the while loop, to give the time that the object set thepropertywhile @sProperty = '?'begin-- Do something to make the object not busy any moreselect * from ds_mds_tab-- Get the property from a objectexec @iRetVal = sp_OAGetProperty @iObject, 'ExtraInfo_FromB',@sProperty OUTIF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG2: Source: ' + @sSource + ' Description: ' +@sDescriptionprint @sLogendprint @sProperty-- Write it to the logexec ds_sp_writetodslogger 'ple', @sPropertywaitfor delay '00:00:01.00'endexec sp_OADestroy @iObject
View 1 Replies
View Related