2

I'm using Windows 7, with PostgreSQL 9.3.6 installed and PostGIS 2.1.5. While I wish I could get everything into PostgreSQL, I can't and need to connect to another server running SQL Server. I need to use a foreign data wrapper for this, and I've installed ogr_fdw as described here. I'm trying to connect a non-spatial table in the foreign SQL Server to my PostgreSQL, and will then join that foreign table to a PostgreSQL spatial table.

From the instructions, I'm unclear how to setup the parameters correctly to make the connection (assuming it is even possible to get it working), and I don't understand how to use the ogr_fdw_info utility. For example, I tried:

ogr_fdw_info -s dbname='databasename' host='servername' user='SQL-serverusername'

but that doesn't get me anywhere. Does anyone know how to connect to a foreign SQL server table using this ogr_fdw library?

With Paul's suggestion, I tried using ogrinfo like this:

ogrinfo "MSSQL:server=ServerName;database=DatabaseName;UID=SQLServerUserName;PWD=SQLServerUserNamePassword"

When I tried that, at least I got

Error initializing the metadata tables: [Microsoft][ODBC Server Driver][SQL Server]CREATE TABLE permission denied in database 'DatabaseName'.

To me that means at least I'm sort of connecting, but not completely. The SQL Server Admin gave me a username with only read rights, and that seems logical since I only want to read one non-spatial table and put it as a foreign table inside my PostgreSQL database on my own machine. I'll have to make sure I can connect using ogrinfo before I can begin to understand how to use ogr_fdw. I tried adding "estimatedmetadata=true" at the end of my ogrinfo statement, but that didn't make a difference. I don't understand exactly why the error would be in creating a table, since I'm only wanting to connect, not create. Any additional hints on getting this connection to work?

With Paul's suggestion, I added the environment variable to my Windows machine:

MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=No

and now the error has gone away. Then I also found I needed to add a second environment variable:

MSSQLSPATIAL_LIST_ALL_TABLES=YES

This enabled me to correctly connect to the SQL server using OGRINFO and see the non-spatial tables. Now however, when I try:

ogr_fdw_info -s "MSSQL:server=ServerName;database=DatabaseName;UID=SQLServerUserName;PWD=SQLServerUserNamePassword"

It says it can't connect to that data source. I'm puzzled since the connection string works fine with OGRINFO, but not OGR_FDW_INFO.

Improve this question

2 Answers 2

Reset to default
1

The main thing is getting a correct connection string, so you're doing the right thing playing w/ ogr_fdw_info until you get a connection working.

 "MSSQL:server=localhost\SQL2012Express;database=ProSpatial;trusted_connection=yes;"

Here's an OGR connection string I found online, and there's another on here at GIS.SE, Shapefile to MSSQL with ogr2ogr fails to make connection

You can also test independently using ogrinfo to figure out what a good connection string is. Once you do, you should be able to work through the rest of the process in FDW.

Improve this answer
5
  • Thank you @Paul Ramsey for those hints. I'm adding an update to my question.
    – Jan
    Apr 23, 2015 at 20:57
  • 1
    Looks like you need to apply the OGR config option "MSSQLSPATIAL_USE_GEOMETRY_COLUMNS" to "NO", or it will try and create a spatial_ref_sys table for you, which you don't have permissions to do.
    – Paul Ramsey
    Apr 23, 2015 at 22:17
  • Thank you again Paul. I've managed to get ORGINFO to work, but when I try the same connection string with OGR_FDW_INFO, it just returns "Could not conect to source <my connection string>. Ideas on what to try?
    – Jan
    Apr 28, 2015 at 23:32
  • It's probably not reading the special config option from the environment, which leaves you entirely out of luck until I update it to accept config options (similar problems would hurt the actual FDW connection)
    – Paul Ramsey
    Apr 29, 2015 at 2:54
  • Ok, thanks Paul, and I greatly appreciate your work with PostGIS in general.
    – Jan
    Apr 29, 2015 at 3:00
1

Jan,

Not sure when you downloaded that binary. In first packaging I made a mistake with the 9.3 build and left out the ODBC driver which is needed for the MSSQL connection. Someone alerted me when they tried to connect to MS Access and I since fixed the issue.

Also if you upgraded PostGIS since, it may have overwritten the libgdal driver and the one that comes packaged with PostGIS does not (yet anyway) include ODBC support. In 2.2 I plan to build ODBC support in.

Thanks, Regina

Improve this answer
1
  • Thank you Regina. The ogr_fdw I'm using has a date of Jan 2, 2015. I'm not sure if that's the latest. Maybe I just need to wait until 9.5 and PostGis 2.2. Thank you for all your work!
    – Jan
    May 1, 2015 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.