[gdal-dev] MSSQL2008 Driver and OGR/Python

[geographika]

Hi,

When working with the SQL Server 2008 OGR driver I presume it is 
necessary to create the following metadata tables?

geometry_columns
spatial_ref_sys

There appears to be no way to do this automatically in Python, but if I 
import a single dataset into the database it is created automatically. I 
can then use:

conn_string = "MSSQL:server=W08-SQL08;database=dbname;Integrated 
Security=true;"
ds = ogr.Open(conn_string)
lyr = ds.GetLayerByName('testdata')

It would be nice to be able to connect to a layer without having to 
register it with the geometry_columns table using a connection string 
such as:

"MSSQL:server=W08-SQL08;database=dbname;Integrated 
Security=true;tables=myschema.testdata(GEOMFIELD)"

As this is not currently possible I manually added a record to 
geometry_columns for an existing spatial table in my database. This is 
in a separate schema so I used the following SQL:

INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema] 
,[f_table_name], 
[f_geometry_column],[coord_dimension],[srid],[geometry_type])
VALUES ('DbName', '*myschema*', 'testdata', 'GEOMFIELD', 2, 32768, 
'MULTIPOLYGON')

However using SQL Profiler when trying to connect to the layer it always 
tries to find this layer in *dbo*.

exec DbName..sp_columns N'testdata',N'*dbo*',N'DbName',NULL

As it does not exist in dbo the connection never succeeds, and I cannot 
connect to any of the layers in the database.
I can add this to trac if it is an issue - I just want to first make 
sure I've not made any obvious errors.

Regards,

Seth Girvin

--
web:http://geographika.co.uk
twitter: @geographika


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110207/a524aa6e/attachment.html