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:
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:
and now the error has gone away. Then I also found I needed to add a second environment variable:
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.