<br><br><div class="gmail_quote">2011/2/7 geographika <span dir="ltr"><<a href="mailto:geographika@gmail.com">geographika@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div text="#000000" bgcolor="#ffffff">
Hi,<br>
<br>
When working with the SQL Server 2008 OGR driver I presume it is
necessary to create the following metadata tables?<br>
<br>
geometry_columns <br>
spatial_ref_sys<br>
<br>
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: <br>
<br>
conn_string = "MSSQL:server=W08-SQL08;database=dbname;Integrated
Security=true;"<br>
ds = ogr.Open(conn_string)<br>
lyr = ds.GetLayerByName('testdata')<br></div></blockquote><div><br>Hi Seth,<br><br>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.<br>
<br> </div><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div text="#000000" bgcolor="#ffffff">
<br>
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:<br>
<br>
"MSSQL:server=W08-SQL08;database=dbname;Integrated
Security=true;tables=myschema.testdata(GEOMFIELD)" <br>
<br>
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:<br>
<br>
INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema]
,[f_table_name],
[f_geometry_column],[coord_dimension],[srid],[geometry_type]) <br>
VALUES ('DbName', '<b>myschema</b>', 'testdata', 'GEOMFIELD', 2,
32768, 'MULTIPOLYGON')<br>
<br>
However using SQL Profiler when trying to connect to the layer it
always tries to find this layer in <b>dbo</b>. <br>
<br>
exec DbName..sp_columns N'testdata',N'<b>dbo</b>',N'DbName',NULL<br>
<br>
As it does not exist in dbo the connection never succeeds, and I
cannot connect to any of the layers in the database. <br>
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. <br>
<br></div></blockquote><div><br>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.<br>
<br><br>Best regards,<br><br>Tamas<br> <br></div></div><br><div style="visibility: hidden; left: -5000px; position: absolute; z-index: 9999; padding: 0px; margin-left: 0px; margin-top: 0px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 130%;" id="avg_ls_inline_popup">
</div>