November 27, 2009 at 5:09 am
Hi,
I am trying to run a DLL from a function. It has worked for years on a SQL2005 machine, but I can't get it running on SQL2008!
The SQLDLL has been successfully registered with regsvr32.
The value returned in @hnd = -2147221005 (when the line --return @hnd is enabled)
Which superbrain can help!!!!
Thanks,
Raymond
ALTER FUNCTION [dbo].[fx_Traveltime](
@pcFrom CHAR(6),
@pcTo CHAR(6),
@calculationMode INT
)
RETURNS INT
AS
BEGIN
DECLARE @ret INTEGER
IF @calculationMode >=0 OR RTRIM(@pcFrom)='' OR RTRIM(@pcTo)=''
SET @ret = ABS(@calculationMode)
ELSE
BEGIN
--bereken dll initieren
DECLARE @retVal CHAR(200), @comHandle INT, @hnd INT
EXEC @hnd = sp_OACreate 'SQLDLL.TravelManager', @comHandle OUTPUT, 5
IF @hnd <> 0
BEGIN
SET @ret = 10000+ABS(@calculationMode)
--return @hnd
END
ELSE
BEGIN
EXEC @hnd = sp_OAMethod @comHandle, 'Traveltime', @retVal OUTPUT, @pcFrom, @pcTo
IF @hnd <> 0
SET @ret = 20000+ABS(@calculationMode)
ELSE
IF @retVal<0
SET @ret = 30000+ABS(@calculationMode)
ELSE
SET @ret = FLOOR((@retVal+30)/60)
EXEC @hnd = sp_OADestroy @comHandle
END
END
RETURN @ret
END
November 29, 2009 at 2:24 pm
Did you try to use sp_OACreate procedure to test the functionality? Try to use wscript.shell object.
EXEC @hr = sp_OACreate 'WScript.Shell'
November 30, 2009 at 3:04 am
Thanks for your reply.
If I run this:
EXEC @hnd = sp_OACreate 'WScript.Shell'
return @hnd
the return value is: -2147211484
October 4, 2010 at 11:33 pm
This is the permission issue. Make sure COM object property are set up properly and the account running the SQL script has sufficient permission to launch the COM application. In the identity tab choose right option for your configuration. Use Administrative Tools-> Component Services->Locate your COM->Property
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply