Mappoint 2004 - SQL 2005 - CLR Integration

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 =3D String.Empty;


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


try
{
if (calcUnits.ToUpper() =3D=3D  kilometerMatchString
)
{
map.Application.Units =3D GeoUnits.geoKm;
unitAppend =3D kilometerTag;
}
else
{
map.Application.Units =3D GeoUnits.geoMiles;
unitAppend =3D mileTag;
}
startResults =3D map. FindAddressResults(f
romStreet,
fromCity, "", fromStateProv, fromZipPostal, fromCountry);
finishResults =3D map. FindAddressResults(t
oStreet,
toCity, "", toStateProv, toZipPostal, toCountry);
if (startResults.ResultsQuality =3D=3D
 GeoFindResultsQualit
y.geoAllResultsValid ||
startResults.ResultsQuality =3D=3D
 GeoFindResultsQualit
y.geoFirstResultGood)
{
if (finishResults.ResultsQuality =3D=3D
 GeoFindResultsQualit
y.geoAllResultsValid ||
finishResults.ResultsQuality =3D=3D
 GeoFindResultsQualit
y.geoFirstResultGood)
{
startLoc =3D  (Location)startResul
ts.get_Item(ref
index);
finishLoc =3D
 (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() =3D=3D  tripTimeMatchString)

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


}
finally
{
// Clear ALL mappoint objects
map =3D null;
startResults =3D null;
finishResults =3D null;
startLoc =3D null;
finishLoc =3D 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 =3D 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
.GetCurrentContext=
=ADInfo(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.=20


Can anybody provide any input on this?