This project is read-only.

[off-topic] Can MS-SQL 2005 access MS-SQL 2000?

Topics: User Forum
Nov 28, 2006 at 5:49 PM
Hi folks,

I had already installed MSSQLSpatial under MSExpress 2005 and it's running fine.

But now I have to access a point table(ID, X and Y columns) which is at MS-SQL2000 database and then intersect these points on a polygon table at SQL 2005.

The 1st thing I was thinking to do is to create a view at the 2005 database pointing to the 2000 table.

Is it possible to do? Does anybody had tried this before?

best
Nov 29, 2006 at 9:55 AM
See Distributed Queries.

Best regards,
Ricardo Stuven.
Nov 29, 2006 at 1:54 PM
Thanks a Lot!

How do I did it:

1. enter 'MS SQL Server 2005/Configuration Tools/SQL Server Surface Area Configuration' and check 'Enable OPENROWSET and OPENDATASOURCE support'

2. connect to SQL 2005 a run a query like this:
SELECT * FROM OPENDATASOURCE('sqloledb',
'Server=(local);Database=dbMyDataBase;User Id=root;Password=****;').dbMyDataBase.root.MyPointTable

Very nice and easy.

thanks.
Mar 15, 2007 at 6:32 PM
Could you share your SQL query or stored procedure to show how your spatial queries look like?

I am running into the same thing. I would like to run some spatial query (like, within, contains) from polygon which is stored in one of the spatial tables but points are in one non-spatial table which has id, x, y coordinates. They all in SQL 2005 which make it little bit easier.

Thanks,
Adrian
Mar 16, 2007 at 9:08 PM

Could you share your SQL query or stored procedure to show how your spatial queries look like?


In order to use MsSqlSpatial functions from SQL Server 2000 (not just tables), you have to use OPENROWSET with a pass-through query. For example:
SELECT a.*
FROM OPENROWSET('SQLOLEDB', '<connection string>',
   'SELECT ST.Centroid(geom) FROM mytable'
) AS a

Best regards,
Ricardo Stuven.
Mar 16, 2007 at 10:22 PM
Thanks Ricardo. This is not exactly I am looking for. Here is my query but it didn't give me what I would like to get. The Offices table contains Lat/Long as float data type and I would like to get points within the polygon passed in as text or get it from other table. Please help.

SELECT id, name, Latitude, longitude
FROM Offices where
ST.Contains (ST.PolygonFromText('POLYGON((33 -80, 35 -90, 40 -100, 50 -110, 45 -90, 33 -80))',32214),
ST.GeomFromText('POINT(' + CAST(ISNULL(Latitude,0) AS nvarchar(15)) + ' ' + CAST(ISNULL(Latitude,0) AS nvarchar(15)) + ' )',32214))=1;

One thing that I have treid was to regenerate a column call the_geom using the data from lat/long column and then I can run query like this it worked. But if I can avoid of adding a geometry column that would be great.

SELECT id, name, Latitude, longitude
FROM Offices where
ST.Contains (ST.PolygonFromText('POLYGON((33 -80, 45 -90, 50 -110, 40 -100, 35 -90, 33 -80))',32214), the_geom) = 1



Thanks,
Adrian