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(fromStreet, fromCity, "", fromStateProv, fromZipPostal, fromCountry);
finishResults = map.FindAddressResults(toStreet, toCity, "", toStateProv, toZipPostal, toCountry);
if (startResults.ResultsQuality == GeoFindResultsQuality.geoAllResultsValid ||
startResults.ResultsQuality == GeoFindResultsQuality.geoFirstResultGood)
{
if (finishResults.ResultsQuality == GeoFindResultsQuality.geoAllResultsValid ||
finishResults.ResultsQuality == GeoFindResultsQuality.geoFirstResultGood)
{
startLoc = (Location)startResults.get_Item(ref index);
finishLoc = (Location)finishResults.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\CVSQLMappointAccess.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 [CVSQLMappointAccess].[CVSQLMappointAccess.CVMapPointUtility].[CalcDrive]
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.RuntimeLicenseContext .GetLocalPath(String fileName)
at System.ComponentModel.Design.RuntimeLicenseContext .GetSavedLicenseKey(Type type, Assembly resourceAssembly)
at System.ComponentModel.LicenseManager.LicenseIntero pHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)
at CVSQLMappointAccess.CVMapPointUtility.CalcDrive(St ring 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

Hi Jody,

I am currently having the an exact problem, I have registered my dll with unsafe, and when I try to run the CLR, I get

System.UriFormatException: Invalid URI: The URI is empty. ....

I am making a reference to adodb.dll, and when I take out the line of code that instatiated a new instance of adodb.Connection, it works fine.

I would really appreciate if you could tell me how you got around it.

Thanks

Michael