Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

A domain error occurred in Sql server 2005 while updating the data Expand / Collapse
Posted Monday, April 22, 2013 7:21 AM


Group: General Forum Members
Last Login: Monday, September 21, 2015 1:05 AM
Points: 14, Visits: 47
I have created a function like the one below

Create FUNCTION fn_Calc
(@Lat1 Float,
@Lng1 Float,
@Lat2 Float,
@Lng2 Float)

Declare @x as Float
Declare @y as Float
Declare @Distance as Float

Select @x = (SIN(RADIANS(@Lat1)) * SIN(RADIANS(@Lat2)) + COS(RADIANS(@Lat1)) * COS(RADIANS(@Lat2)) * COS(ABS((RADIANS(@Lng2)) - (RADIANS(@Lng1)))))
Select @y = ATAN((SQRT(1-(POWER(@x,2))) / @x))

Select @Distance = (1.852 * 60.0 * ((@y / PI()) * 180)) / 1.609344

RETURN @Distance


I am using the above function to update a column in a table like below:

Update test set calc = dbo.fn_Calc( cast(Lat as float), cast(Long as float), dblLat, dblLong)

While running the above query I got the error.

"A domain error occured."

What can be causing this error?

Roshan. N
Post #1444977
Posted Tuesday, April 23, 2013 10:26 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 24, 2015 12:20 PM
Points: 1,064, Visits: 2,582
I had this problem once before, but I was using a slightly different formula for calculating distances between lat/long pairs. Unfortunately, I no longer work for my employer at the time and don't have the notes I made of this issue to be more specific, so I only vaguely recollect the circumstances. IIRC, I discovered that there were certain input values that would cause the T-SQL COS function to return results just slightly outside the -1 < x < 1 range. This cascaded through the calculation and resulted in an input to the ACOS function that also fell outside the -1 < x < 1 range, which generated the domain error. I corrected the problem by adjusting any results of the COS function that fell outside the range to 1 or -1 as appropriate.

The way I finally diagnosed the problem was by running a query that included a column for each individual part of the formula, building up from column to column until I had the whole result. When I ran this query on the dataset that caused the domain error, I narrowed down the query to include only the rows that caused the error. Then, I reran the query, taking columns out of the query (starting from the whole formula and working down to the pieces) one by one until I didn't get the error any more. At that point, I looked at the results and noticed the out-of-range result for the COS function.

I've tried a few things to see if I could replicate the out-of-range result for the COS function on my SQL Server 2008R2 instance, but haven't been able to do so.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1445564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse