We would like to thank Jeff Crumbley of IILogistics for providing many of these steps
and informing us that Microsoft has finally released a
64-bit OLEDB for ODBC driver.
For those who have not experienced the torture of this situation - let me start with a little background.
First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If
however you had a SQL Server 2005 64-bit 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 2005 64-bit.
- 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) (If you are running Vista 64-bit or Windows 2008 64-bit these are included already (or possibly in SP1))
- Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (seems to also work fine against 8.3/8.4 if you are running that) and place
the dlls from psqlodbc_AMD64 available from
There is a newer compiled 64-bit ODBC driver at http://code.google.com/p/visionmap/wiki/psqlODBC If you are using this newer driver the use PostgreSQL 64-bit ODBC Drivers for the driver name instead of what we have below. The newere driver doesn't seem to handle data type conversion quite as well as the older.
- Run the psqlodbcwAMD64.reg file
- Create a System DSN in the 64-bit Data Source (ODBC) - alternatively you can skip this and use and embedded file DSN in
SQL Server 2005 that we will outline in the next step.
- Create a Linked Server in SQL Server - below is a sample script that creates a PostgreSQL Linked Server in Microsoft SQL Server
After that you should see the linked server in SQL Server 2005 Management ->Server Objects ->Linked Server and from there
you can fiddle further with the settings. You should also be able to expand the PostgreSQL linked server and see the tables and views.
EXEC master.dbo.sp_addlinkedserver @server = N'NAMEOFLINKEDSERVERHERE', @srvproduct=N'PostgreSQL AMD64A',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;database=pgdatabase;pwd=somepassword'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NAMEOFLINKEDSERVERHERE',
- To test out the linked server - you can run the sample query below in SQL Server:
'SELECT * From information_schema.tables')
Keep in mind that the PostgreSQL 64-bit ODBC is marked as experimental, but we have had good success with it on an Intel processor based
64-bit Windows 2003 running SQL Server 2005 64-bit.
This article is a bit of a companion to our article on Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit In this article we shall demonstrate using Microsoft SQL Server 2005/2008 OPENQUERY AND OPENROWSET to add, delete and update
Tracked: Feb 19, 12:33
Tracked: Jul 23, 13:37
Tracked: Feb 21, 19:02
Tracked: Apr 15, 16:43