Showing posts with label point. Show all posts
Showing posts with label point. Show all posts

Tuesday, March 27, 2012

Calling SSIS packages from within a Script task

Hello,

I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?

In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.

I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.

Any help would be greatly appreciated.

Thank you,

Gordon Radley

Sample code to execute a Package from Script task

Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub

Hope this helps.

Thanks,
Loonysan

|||

Yes - it definately helps. Thanks for the quick reply.

Do I need to add any additional references if I'm running this from within a Script task in SSIS?

Thanks again!

|||

The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.

As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.

For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.

-Doug

|||

Doug,

We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.

Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.

-Jamie

|||

Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.

When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.

Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.

So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!

=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.

|||

I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.

In this script task, the package is called as below.

Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application

Dim pL As Package = app.LoadPackage(Lpkg, Nothing)

If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then

pL.Execute()

else

...Call another package.

End if.

I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?

Calling SSIS packages from within a Script task

Hello,

I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?

In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.

I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.

Any help would be greatly appreciated.

Thank you,

Gordon Radley

Sample code to execute a Package from Script task

Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub

Hope this helps.

Thanks,
Loonysan

|||

Yes - it definately helps. Thanks for the quick reply.

Do I need to add any additional references if I'm running this from within a Script task in SSIS?

Thanks again!

|||

The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.

As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.

For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.

-Doug

|||

Doug,

We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.

Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.

-Jamie

|||

Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.

When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.

Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.

So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!

=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.

|||

I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.

In this script task, the package is called as below.

Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application

Dim pL As Package = app.LoadPackage(Lpkg, Nothing)

If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then

pL.Execute()

else

...Call another package.

End if.

I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?

Sunday, March 25, 2012

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
Hao
It is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPendpointTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>
|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone there
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent
|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.co m...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>
|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/WindowsServer/en/library/b3a029a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
HaoIt is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPe...tTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone ther
e
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.com...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/Windo...a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows200...isting/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Sunday, February 12, 2012

Calculating distance based on latitude and longitude

I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.

Does anyone have any algorithms in T-SQL to do this?

if this were easy to do, esri/mapinfo wouldn't charge big bucks for their GIS libraries. People get PhD's in geodesia!

Now, there is a solution.

Search your registry for MapX. MapInfo's MapX ocx is distributed in a few different software packages. I thought it came in Microsoft Office. But I see it also comes in Crystal XI.

Discussion about the usage of this library is not appropriate for this forum and I would direct you to Mapinfo.com.

I would be more than happy to develop a wrapper for you if you are willing to pay. I charge US$150 an hour for GIS development. probably looking at 3 hours.

|||
The SQL Server 2005 Samples download contains the source
for the Spatial Data Library which I believe has
a C# function for calculating the distance between two
points from their lat/lons.
|||

it appears there is one in the SQL class. result is in Arc Minutes.

should be ok for small distances, and not near the equator. it doesn't take into account 'equatorial bulge'

in most applications it should be okay, just be aware of its propensity for error.

the algorithm doesnt look that hard to translate to straight T-SQL for increased speed:


CREATE FUNCTION CARTESIAN_PI() RETURNS DECIMAL(36, 35)
AS
BEGIN
RETURN 3.14159265358979323846264338327950288
END
GO
CREATE FUNCTION DBO.CARTESIAN_EPSILON() RETURNS DECIMAL(15,15)
AS
BEGIN
RETURN 0.000000000000001
END
GO
CREATE TYPE LATLONGUNIT FROM DECIMAL(26, 20);
GO
CREATE FUNCTION CARTESIAN_COS(@.arg LATLONGUNIT) RETURNS REAL
AS
BEGIN
DECLARE @.result REAL;
IF ABS(@.ARG-DBO.CARTESIAN_PI()) < DBO.CARTESIAN_EPSILON()
SET @.RESULT = 0
ELSE
SET @.RESULT = COS(@.arg)
RETURN @.result;
END
GO
CREATE FUNCTION DistanceLatLon(@.lat1 LATLONGUNIT, @.lon1 LATLONGUNIT, @.lat2 LATLONGUNIT, @.lon2 LATLONGUNIT) RETURNS REAL
AS
BEGIN
DECLARE @.d2r REAL
DECLARE @.nx1 REAL
DECLARE @.ny1 REAL
DECLARE @.nz1 REAL
DECLARE @.nx2 REAL
DECLARE @.ny2 REAL
DECLARE @.nz2 REAL

SET @.d2r = DBO.CARTESIAN_PI() / 180;
SET @.nx1 = DBO.CARTESIAN_COS(@.lat1 * @.d2r) * DBO.CARTESIAN_COS(@.lon1 * @.d2r);
SET @.ny1 = DBO.CARTESIAN_COS(@.lat1 * @.d2r) * SIN(@.lon1 * @.d2r);
SET @.nz1 = SIN(@.lat1 * @.d2r);
SET @.nx2 = DBO.CARTESIAN_COS(@.lat2 * @.d2r) * DBO.CARTESIAN_COS(@.lon2 * @.d2r);
SET @.ny2 = DBO.CARTESIAN_COS(@.lat2 * @.d2r) * SIN(@.lon2 * @.d2r);
SET @.nz2 = SIN(@.lat2 * @.d2r);
RETURN (60 * 2 * (1 / @.d2r) * ASIN(SQRT((@.nx1 - @.nx2) * (@.nx1 - @.nx2)
+ (@.ny1 - @.ny2) * (@.ny1 - @.ny2)
+ (@.nz1 - @.nz2) * (@.nz1 - @.nz2)
) / 2));
END

|||

Cool!

Might want to double check the precisions. Real number precision confuses the hell out of me. I never feel 100% comfortable that I have allocated enough for the application.

Also. . . if you are doing alot of this, you might want to investigate MapInfo or ESRI for some really slick GIS db extensions.