[mapserver-users] Mapserver and Microsoft SQL Server 2008

[Chris King]

Thanks for the reply, I did notice the geometry_columns table was missing
and at first when I added it, I got SQL errors, but after a few tweaks, I no
longer get the errors when I have a geometry_columns table in my database. I
narrowed the error down to the layer.open() command, and found where the
error occurs in the source code of the sql server plugin dll. For some
reason it cannot locate the geometry column in either the table itself or
the geometry_columns table, even though both are specified as 'the_geom'.
I'm looking into the security now, I did notice that I could not set the
owner of the geometry_columns table... CREATE TABLE geometry_columns OWNER
TO owner, SQL isn't liking the 'OWNER' part... Otherwise I'll have another
look at the webserver permissions, but those have never been a problem... 
At a bit of a loss here and running out of ideas...
Thanks
Chris

-----Original Message-----
From: Brent Wood [mailto:pcreso at pcreso.com] 
Sent: 11 April 2008 10:49 PM
To: Chris King
Subject: Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008


--- Chris King <whereismyshuttle at gmail.com> wrote:

> Hi
> 
> I manually imported my data from postgre into SQL 2008 and the map 
> displays fine. I am now executing a the layer.queryByPoint () command 
> followed by the
> layer.getResults() command at which point the program fails and gives 
> the following error:
> 
> msMSSQL2008LayerGetItems(): Query error. msMSSQL2008LayerGetItems: 
> tried to find the geometry column in the results from the database, 
> but couldnt find it.  Is it miss-capitialized? 'the_geom'
> 
> I know when you create the tables in the postgre database you 
> explicitly add the geometry column after the table creation


I'm guessing here, never having used SQL Server....

This is like it is with PostGIS because PostGIS is not fully integrated with
Postgres, but a third party addon. In SQL Sever 2008 I would assume you'd
use "alter table add ..." to add a new geometry column, like you do for any
other native datatype.

Assuming MS actually followed someone else's standard for a change, your
database should have a geometry_columns table with the metadata of your
geometry column & its table there. See if that all seems OK first. My guess
is that mapserver will be reading some info about the geometry from there...


So your first check should be that the geometry column exists in both the
table you created & the geometry_columns table.

Other than that, I can only think of a permissions problem somewhere? Is the
web server user running the mapserver cgi able to access the SQL db/table
OK??

Someone who has more SQL Server expertise will have to advise any further
:-)


HTH,

  Brent Wood

> 
> ie: SELECT 
> AddGeometryColumn('','ct_main_points','the_geom','-1','POINT',2);
> 
> when I tried this in SQL 2008 the function was not recognized and I 
> cannot find the SQL equivalent function. So I simply added the 
> geometry column when I created the table, now I have a feeling that is 
> what is causing this error.
> 
> If anyone has seen this error or knows of the AddGeometryColumn SQL 
> 2008 equivalent function, please could you let me know.
> 
> Thanks
> Chris
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>