<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#ffffff">
Thanks Tamas for the quick reply. <br>
I've been trying a few more combinations, and I now have it working.
<br>
<br>
In the geometry_columns table the first record works - when I
duplicate the schema name in the the f_table_schema and f_table_name
fields. The second does not. <br>
In fact I can put any entry in the f_table_schema field - it appears
to be ignored. <br>
<br>
<br>
[f_table_catalog], [f_table_schema] ,[f_table_name],
[f_geometry_column],[coord_dimension],[srid],[geometry_type]<br>
<table>
<tbody>
<tr>
<td>DbName</td>
<td>any value can be put here<br>
</td>
<td>schemaname.mytable</td>
<td>GEOMFIELD</td>
<td>2</td>
<td>32768</td>
<td>MULTIPOLYGON</td>
</tr>
<tr>
<td>DbName</td>
<td>schemaname</td>
<td>mytable</td>
<td>GEOMFIELD</td>
<td>2</td>
<td>32768</td>
<td>MULTIPOLYGON</td>
</tr>
</tbody>
</table>
<br>
The following now works (with the first record - not the second):<br>
<br>
conn_string = "MSSQL:server=W08-SQL08;database=DbName;Integrated
Security=true;"<br>
ds = ogr.Open(conn_string)<br>
lyr = ds.GetLayerByName('schemaname.mytable') #schema name always
has to be passed here<br>
<br>
If this is not the intended behaviour I can create a ticket,
otherwise thanks for your time (and drivers!). <br>
<br>
Regards,<br>
<br>
Seth<br>
<br>
<div class="moz-text-html" lang="x-western"> <font color="#888888">--<br>
web:</font><a href="http://geographika.co.uk"><font
color="#888888"> </font>http://geographika.co.uk</a><br>
<font color="#888888">twitter: @geographika<br>
</font><br>
</div>
<br>
<br>
On 07/02/2011 13:26, Tamas Szekeres wrote:
<blockquote
cite="mid:AANLkTimUdTr2XxPSQVk8KP8X9xV0boCG+0AcCK-1-X1o@mail.gmail.com"
type="cite"><br>
<br>
<div class="gmail_quote">2011/2/7 geographika <span dir="ltr"><<a
moz-do-not-send="true" 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>
</blockquote>
<br>
</body>
</html>