April 24, 2012 at 9:38 am
Hi,
Sorry if I've put this in the wrong forum but I've searched for a solution to this problem for a while now and haven't really got anywhere so was hoping someone can help. The below is simplified but close to what I am trying to do:
I have a 2005 Server with a database containing a table with a list of records which have easting and northing fields stored as int.
I then have a 2008 Server with a database containing a table with a geometry field, which is a set of state boundaries.
The two servers are correctly linked and from the 2008 Server I am able to pull eastings and northings from the 2005 Server, convert them into a point, and use the STContains method to return the state that each easting and northing falls within.
What I am however now trying to do is from the 2005 Server dynamically return the state that a set of eastings and northings fall into. Initially I thought this would be easy so created a function on the 2008 Server that, given an easting and northing, would return the name of the state. However when going over to the 2005 Server and trying to call this function I hit upon the problem that you can't remotely call functions.
Since then I have played around with a number of options but can't seem to get it working as I need. I've tried using stored procedures on the 2005 Server to pass the variables to the function on the 2008 Server but then I can't easily access the results. I don't think I can use a function on the 2005 Server as I can't execute a stored procedure or function from the 2008 Server within it and due to the geometry methods required the processing has to be done by the 2008 Server (I think).
Anyway I suppose more simply what I am trying to do is implement somehow on the 2005 Server the following or any sort of workaround:
select p.*, Server2008.database1.dbo.returnstate(p.easting,p.northing)
from database1.dbo.points p
where returnstate on the 2008 Server is defined as:
CREATE FUNCTION [dbo].[returnstate]
(@easting int,@northing int)
RETURNS varchar(50)
AS
BEGIN
declare @statename varchar(30)
declare @point geometry
set @point = geometry::Point(@easting,@northing,27700)
set @statename = (select statename
from database1.dbo.stateBoundaries
where sp_geometry.STContains(@point) = 1)
return @statename
END
Hopefully I'm being an idiot and there is a simple workaround but if anyone could help that would be great.
Thanks.
April 25, 2012 at 5:01 am
Since that data type isn't supported in 2005, you need to get all the data to filter first on the 2008 server. Have you tried running the same query using OPENQUERY? I'd test that out next. If that doesn't work, it's possible you just can't make this leap.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply