[gdal-dev] MSSQL2008 Driver and OGR/Python

[Tamas Szekeres]

2011/2/7 geographika <geographika at gmail.com>

>  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')
>

Hi Seth,

Those metadata tables are required since MSSQL2008 doesn't have a builtin
way to store all of these information. When importing the first table it
should indeed be created from scratch. With regards to spatial_ref_sys it
could also be populated by using the corresponding postgis script.



>
> 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.
>
>
Specifying the schema in geometry_columns should be working and
myschema.mytable(GEOMFIELD) should also be a working option. So please file
a ticket with this issue if you encounter problems here.


Best regards,

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