Getting the ODBC link working for a SQL server to see a postgres server was fairly easy but had a few gotchas that took me a while to work out. First step is to install the Postgres official ODBC driver and setup a DSN for the server using the standard ODBC interface in windows.
In the options for the ODBC link tick "Use Declare/Fetch" as otherwise the ODBC driver tries to cache the whole table in memory. For huge tables this will fail with errors about memory and unable to read tuples, even if there is spare memory on the machine (the ODBC driver can not access all the system memory - its limited so you get memory errors even when your machine and the postgres machine both have lots of free memory). With this ticked the driver grabs blocks of 100 rows at a time.
You also need to untick the bool as char box so the driver passes the booleans as bits to SQL server 2005 which is how it views them. Otherwise it tries to put "yes/no" in a field which will only accept "1/0"
If using 64 bit Windows you still need the 32 bit ODBC driver - it will not show up in the data(ODBC) section of the control panel but will be accessible through the SQL server business intelligence development studio. Or you can run the 32 bit ODBC manager at C:\WINDOWS\SysWOW64\odbcad32.exe. I never did work out why on 64 bit windows (server 2003) with 64 bit SQL server (2005) it uses the 32 bit ODBC connection.
No comments:
Post a Comment