More

Can't connect to foreign SQL Server table from PosgreSQL using OGR_FDW


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.


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=localhostSQL2012Express;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.


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