Mappoint 2004 - SQL 2005 - CLR Integration

Hello all,

I have a problem that I cannot seem to find allot of information for,
this site came across my searches many times so I figured I should
start here.
I require the ability to be able to get route info (specifically
Distance) from inside a SQL 2005 Function (so that it may be displayed
in a view). I know that it may be rough performance wise but I have no
other option right now (I don't think anyway...)

I created a small class library in .NET2.0 to load into the SQL
Database to handle the calls to the COM+ Mappoint component. I have
made much progress but now I cannot seem to get the Item to work.

Am I missing something here with the Assembly security, or can Mappoint
even be used in this context?

C# Code:

using System;
using System.Globalization;
using Microsoft.SqlServer.Server;
using MapPoint;
namespace CVSQLMappointAccess
{
public static class CVMapPointUtility
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string CalcDrive(
string calcItem,  // Item to calc. TRIPTIME will give you
time, Default/UnMatched will give TRIPTIME
string calcUnits, // Units to calc in, kilometers will give
you KMS, Default/UnMatched will give MILES
string fromStreet,
string fromCity,
string fromStateProv,
string fromZipPostal,
string fromCountry,
string toStreet,
string toCity,
string toStateProv,
string toZipPostal,
string toCountry)
{
string returnValue = String.Empty;

MapClass map = new MapClass();
FindResults startResults = null;
FindResults finishResults = null;
Location startLoc = null;
Location finishLoc = null;
object index = 1;
string  kilometerMatchString
 = "KILOMETERS";
string tripTimeMatchString = "TRIPTIME";
string kilometerTag = "KM";
string mileTag = "MI";
string unitAppend;

try
{
if (calcUnits.ToUpper() ==  kilometerMatchString
)
{
map.Application.Units = GeoUnits.geoKm;
unitAppend = kilometerTag;
}
else
{
map.Application.Units = GeoUnits.geoMiles;
unitAppend = mileTag;
}
startResults = map. FindAddressResults(f
romStreet,
fromCity, "", fromStateProv, fromZipPostal, fromCountry);
finishResults = map. FindAddressResults(t
oStreet,
toCity, "", toStateProv, toZipPostal, toCountry);
if (startResults.ResultsQuality ==
 GeoFindResultsQualit
y.geoAllResultsValid ||
startResults.ResultsQuality ==
 GeoFindResultsQualit
y.geoFirstResultGood)
{
if (finishResults.ResultsQuality ==
 GeoFindResultsQualit
y.geoAllResultsValid ||
finishResults.ResultsQuality ==
 GeoFindResultsQualit
y.geoFirstResultGood)
{
startLoc =  (Location)startResul
ts.get_Item(ref
index);
finishLoc =
 (Location)finishResu
lts.get_Item(ref index);
map.ActiveRoute.Waypoints.Add(startLoc,
"Start");
map.ActiveRoute.Waypoints.Add(finishLoc,
"Finish");
map.ActiveRoute.Calculate();

if (calcItem.ToUpper() ==  tripTimeMatchString)

{
returnValue =
FormatTime(map.ActiveRoute.TripTime);
}
else
{
returnValue =
FormatDouble(map.ActiveRoute.Distance, 2) + " " + unitAppend;
}
map.ActiveRoute.Clear();
}
else
{
returnValue = "End Address Not Found";
}
}
else
{
returnValue = "Start Address Not Found";
}
}
catch (Exception)
{
returnValue = "Error With Mappoint Module";

}
finally
{
// Clear ALL mappoint objects
map = null;
startResults = null;
finishResults = null;
startLoc = null;
finishLoc = null;
}
return returnValue;
}
public static string FormatDouble(double val, int
decimalPlaces)
{
...
}
public static string FormatTime(double val)
{
...
}
}
}

Registering the DLL with SQL 2005:

ALTER DATABASE MyDB SET trustworthy ON
CREATE ASSEMBLY CVSQLMappointAccess
FROM  'C:\SQLAssemblies\CV
SQLMappointAccess.dll'
WITH PERMISSION_SET = UNSAFE;
GO

NOTE: This also automatically adds the Interop.Mappoint.dll into SQL
2005 as unsafe as well.

SQL 2005 Function:

CREATE FUNCTION fn_calc_drive(
@calcItem  NVarChar(20),
@calcUnits  NVarChar(20),
@fromStreet  NVarChar(100),
@fromCity  NVarChar(100),
@fromStateProv NVarChar(100),
@fromZipPostal NVarChar(100),
@fromCountry NVarChar(100),
@toStreet   NVarChar(100),
@toCity   NVarChar(100),
@toStateProv NVarChar(100),
@toZipPostal NVarChar(100),
@toCountry  NVarChar(100))
RETURNS NVarChar(100)
AS
EXTERNAL NAME
& #91;CVSQLMappointAcc
ess].& #91;CVSQLMappointAcc
ess.CVMapPointUtility].[C
alcDrive]
Go

Calling the Function:

Select dbo.fn_calc_drive(
'distance',
'kilometers',
'1027 Wayne Pl',
'Burlington',
'ON',
'CA',
'',
'8 Riverview Ave',
'Mount Pearl',
'NL',
'CA',
'')

Error Received:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'CalcDrive':
System.UriFormatException: Invalid URI: The URI is empty.
System.UriFormatException:
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind
uriKind)
at
System.ComponentModel.Design. RuntimeLicenseContex
t.GetLocalPath(String
fileName)
at
System.ComponentModel.Design. RuntimeLicenseContex
t. GetSavedLicenseKey(T
ype
type, Assembly resourceAssembly)
at
System.ComponentModel.LicenseManager. LicenseInteropHelper
.GetCurrentContextI
nfo(Int32&
fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)
at CVSQLMappointAccess.CVMapPointUtility.CalcDrive(String calcItem,
String calcUnits, String fromStreet, String fromCity, String
fromStateProv, String fromZipPostal, String fromCountry, String
toStreet, String toCity, String toStateProv, String toZipPostal, String
toCountry)

I did a bit of searching regarding Error 6522 and I found mention of
permissions issues. I tried to register the Assemblies with
EXTERNAL_ACCESS but I get the following error:

Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'get_Application' on type
'MapPoint._Application'  in external_access assembly 'Interop.MapPoint'
has invalid attribute 0x1003.

Can anybody provide any input on this?

Thanks,
Jody