We were setting up another SQL Server 2005 64-bit where we needed a linked server connection to our PostgreSQL 9.0 server. This is something we've done before so not new and something we documented in Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit. What was different this time is that we decided to use the latest version of the new PostgreSQL 64-bit drivers now available main PostgreSQL site http://www.postgresql.org/ftp/odbc/versions/msi/. Sadly these did not work for us. They seemed to work fine in our MS Access 2010 64-bit install, but when used via SQL Server, SQL Server would choke with a message:

If you tried to do a query with them. You can however see all the tables via the linked server tab.

The query runs on PostgreSQL fine and we can see it in the logs. The odbc log looks fine, so seems to be an issue when its coming back to SQL Server. So for the time being until we figure out if its an issue on our end or something wrong with the published 64-bit ODBC drivers, we'll be using these http://code.google.com/p/visionmap/wiki/psqlODBC

64-bit Official PostgreSQL drivers

The odbc 64 bit installs both a ANSI and Unicode. We tried both and both gave same errors

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL ANSI(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'
EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL.1', @provstr=N'Driver=PostgreSQL ANSI(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL Unicode(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL.1', @provstr=N'Driver=PostgreSQL Unicode(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Vision Map 64-bit drivers

The visionmap on just installs one so registration looks like (this one works fine)

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Our connection strings -- using the visionmap one look like:

-- The visionmap on just installs one so registration looks like (this one works fine)
EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Test query

Our query looked something like below, but any query we used gave similar error with the PostgreSQL site 64-bit drivers.

SELECT a.*
FROM OPENQUERY(PGSERVER, 'SELECT table_name FROM information_schema.tables') As a;