How to make Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3

If however you had a SQL Server 2008 64-bit server and wished to create a linked server to a PostgreSQL server. You ran into 2 very annoying obstacles.

Obstacle 1:
There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.

Obstacle 2:
All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft. Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.

Below are the steps to get a PostgreSQL linked server working in SQL Server 2008 64-bit:

1. Run WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe - (Available as of 4/4/2008 from: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en).
2. Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (you can try the 8.3 if you are running that) and place the dlls from psqlodbc_AMD64 available from http://www.geocities.jp/inocchichichi/psqlodbc/index.html.
3. Run the psqlodbcwAMD64.reg file.
4. Create a System DSN in the 64-bit Data Source (ODBC).
5. Create a Linked Server in SQL Server to PostgreSQL (I'll explain it in detail below).

OK, now here are the steps to configure a Linked Server in SQL Server 2008:

1. Launch the SQL Server 2008 Management Studio.
2. Under the Server Objects folder, right click on Linked Servers and select New Linked Server.
3. Enter a name for the Linked Server and choose "Other Data Source". Do not use any spaces or special characters in the Linked Server's name.
4. Select "Microsoft OLE DB Provider for ODBC Drivers" from the Provider dropdown list. Enter MSDASQL for the Product Name.
5. In the "Data Source" field, enter the name of the ODBC System data source you wish to use. Note that you have to use exact same name as it shown in the ODBC Data Source Administrator.
6. In the Security page, select the "Be made using this security context". Enter a valid remote user/login (user ID) and password for the remote host.
7. Leave the remaining fields blank and click OK to save the linked server configuration.

Now it's time for testing our configuration. If an error occures, review the error and make changes to the linked server configuration as necessary.

In the Management Studio, expand the linked server node and then expand the Catalogs node. Click on the node for the linked server and expand the Tables to view a table list.

That's it, and you've make a Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3. Congratulation. In the next posting, I'll explain how to use the linked server to manipulate the data in the remote server.