Using Mappoint COM object through SQL Server

I have created a Active X DLL object in VB6 that uses Map Point to calculate the Long/Lat of a given Post Code. When used in a test application, the object performs fine. I am now trying to use the same object within SQL Server 2000 (using the CreateObject syntax). The object initialises fine but as soon as it tries to open an instance of MapPoint.Application it fails with an Active X cant create object error. Is what I'm doing impossible or am I missing something?

Any help would be appreciated.

Thanks.

Can you post a fragment of your code showing how you're initalizing the application object? That may help debug it.

Are you doing this within a DTS or something? One thing that pops in my head is if you have a lot of users hitting your DB, it may be failing due to stress...have a 100 instances of MP open may be causing a problem.

-Rahn

The map point COM object is a basic Active X DLL. It has a Reference in it to the MapPoint 9.0 Object Library (Europe). It has an object variable (Mapt) declared as MapPoint.Application.

There is a method in the object called Start_MapPoint which simply calls
SET Mapt = NEW MapPoint.Application.

The initial lines of the stored procedure are as follows:

CREATE PROCEDURE aaGetLongLat(
@Pcode varchar(20))
AS

DECLARE @Object int
DECLARE @hr int
DECLARE @Property varchar(255)
DECLARE @Return varchar(255)
DECLARE @src varchar(255)
DECLARE @Desc varchar(255)
DECLARE @PropLong float
DECLARE @PropLat float

PRINT 'Creating Object'

-- Create an Object
EXEC @Hr = sp_OACreate 'MapPointServer.clsLongLat',@Object OUT

IF @Hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@src OUT,@Desc OUT
SELECT hr = CONVERT(varbinary(4),@hr),Source = @src,Description = @Desc
RETURN
END

PRINT 'Set Properties'

--Set the Properties
EXEC @Hr = sp_OASetProperty @Object,'PostCode',@Pcode
IF @Hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@src OUT,@Desc OUT
SELECT hr = CONVERT(varbinary(4),@hr),Source = @src,Description = @Desc
RETURN
END

Print 'Starting Map Point'
EXEC @hr = sp_OAMethod @Object, 'StartMapPoint'
IF @Hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Object,@src OUT,@Desc OUT
SELECT hr = CONVERT(varbinary(4),@hr),Source = @src,Description = @Desc
RETURN
END

The rest of the code is irrelevant because it doesn't run past this point in the code.

The output if I run this in Query analyser is:

Creating Object
Set Properties
Starting Map Point
0x800A01AD - ActiveX component can't create object.

I think the problem may be with how mappoint is instantiated within the dll.
I created 2 sample dlls as follows:

1. mpClassApp: creates the mappoint object as mappoint.application
2. mpClassMap: creates the mappoint object as mappoint.map

I put together a very simple SQL script to open up each dll and print out the return error codes.

When opening mpClassMap, it worked fine.
mpClassApp created an error and failed to open MP.

instead of posting all the code here, I threw a zipped file at:
http://home1.gte.net/res05fqx/rahn/projects/MpTest.zip

Let me know what you think. I'm curious to see where this goes and the resolution.
If anyone thinks I'm way off base, I'd like to hear :D

Rahn

Oh, note I'm running XP Pro, SQL Server 2000 Development edition for this. Not sure how much dev edition of the SQL server will make a difference.

The error you are getting in your .application class is due to a typo in your SQL statement. Instead of

exec @Hr = sp_OACreate 'aclsTest,mpClassApp', @Object OUT

it should read:

exec @Hr = sp_OACreate 'aclsTest.mpClassApp', @Object OUT

When I run your test code, I get the same error in both cases, the error message however is NULL. This is the problem I was experiencing when creating an instance of the object in the Initialize Event of the class. That is why I created the StartMapPoint method, to prove that at least my object was being created and then you seem to get proper error messages returned.

:oops:
guess typos happen when I'm trying something while tired and the wife is talking to me in the background.

Interesting though, the mpClassMAP method did fire up mappoint for me.
I'd guess I could use some better error handling, which would point out where the error is coming from instead of returning a generic OLEDB message.

I'll try to play with it more tonight or tomorrow.

I look a quick look on MSDN for your error number and got this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;298926

(Yes, I know this article is for terminal services/IIS.)
Implies that it's a permission problem. Have you played with the permissions at all to see if that makes a difference?

I added a method to the mpClassApp, so I could try calling it to simulate your SQL better.
No such luck...worked fine with my machine. Which leads me back to my previous post of having the right permissions.

I've updated the ZIP file at the location above with the new class code and the new SQL statements.
The method works great :)

-Rahn

I really appreciate this help.

I'm running all this stuff locally on the Windows 2000 Server that has the SQL Server database on it. It also has Map Point installed on it locally.

I have proved my object is being created OK and it is the Map Point object that is failing. Not being very techy when it comes to Comms/Networking etc, please could you tell me where I need to set permissions to allow SQL Server to access the Mappoint application objects.

Thanks

I've done it!!!!!

You were right, it was a permissions problem on the server. I added the DLL's to the COMs lists and it worked first time.

Thanks again for all you help, it's much appreciated. :D