Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

Friday, April 25. 2008

Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

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.

UPDATE: Since we wrote this article, PostgreSQL now comes with a 64-bit ODBC driver you can download from http://www.postgresql.org/ftp/odbc/versions/msi/. To use these make sure to use MSDASQL.1 instead of MSDASQL. In fact you should probably be using MSDASQL.1 anyway. refer to our newer article SQL Server 64-bit Linked Server woes

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 http://www.geocities.jp/inocchichichi/psqlodbc/index.html 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 2005 64-bit.


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',
        @useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

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.

To test out the linked server - you can run the sample query below in SQL Server:


SELECT * 
    FROM 
        OpenQuery(NAMEOFLINKEDSERVERHERE, 
            '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.

Posted by Leo Hsu and Regina Obe in intermediate, q&a, sql server at 01:28 | Comments (21) | Trackbacks (4)

Trackbacks

Trackback specific URI for this entry

Using Microsoft SQL Server to Update PostgreSQL Data
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

Weblog: Postgres OnLine Journal
Tracked: Feb 19, 12:33

PingBack

Weblog: www.postgresonline.com
Tracked: Jul 23, 13:37

PingBack

Weblog: www.postgresonline.com
Tracked: Feb 21, 19:02

PingBack

Weblog: www.postgresonline.com
Tracked: Apr 15, 16:43

Comments

Display comments as (Linear | Threaded)

Hi. I am actually having trouble making a linked server to Postgres even with the 32-bit server. It all seems to work (and I can use the same ODBC driver easily to import the PostgreSQL data to Microsoft Access), but when I try to query a short simple table, I get this kind of message:
select StoreCode, Date, ActualBanked from Postgres.rmsweb.[public].Banking

returns
OLE DB provider "MSDASQL" for linked server "Postgres" returned message "ERROR: syntax error at or near ""Col1004"";
Error while executing the query".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "Tbl1002"."StoreCode" "Col1004","Tbl1002"."Date" "Col1005","Tbl1002"."ActualBanked" "Col1007" FROM "rmsweb"."public"."Banking" "Tbl1002"" against OLE DB provider "MSDASQL" for linked server "Postgres".

(it seems to be doing some kind of a column and table name translation and getting confused)
Any thoughts? thanks
Mark Lockett

#1 Mark Lockett (Homepage) on 2009-06-30 20:32 (Reply)

Hello,

I had the same problem and I found the solution and post it to my Blog here: http://blog.danielcosta.pt/?p=492

Hope this help!

Daniel Costa

#1.1 Daniel Costa (Homepage) on 2011-11-16 18:39 (Reply)

Hi,
we are using OLEDB provider for PostgreSQL (http://www.pgoledb.com). Linked Servers and replication are working very well with the provider (see example at http://www.pgoledb.com/forum/viewtopic.php?f=4&t=6).

Regards,
K@PGNP

#2 barsung (Homepage) on 2009-08-28 02:04 (Reply)

Thanks for the report. We'll need to try this out ourselves and report on it. we'll try to do this in our August/September 2009 issue.

#2.1 Leo on 2009-08-29 07:53 (Reply)

I have a similiar problem on 32 bit. Can you please help?

Regards
Wolfgang

I used (32 bit version):

SELECT PID, value
FROM OPENQUERY(PGCounter, 'SELECT * from "PostGreDB".originalTable ')

worked somehow (i can see data in a MSSQL Server Management Express View).

If i use
SELECT PID, value
FROM OPENQUERY(PGCounter, 'SELECT PID, value
from "PostGreDB".originalTable ')

the error is
OLE DB provider "MSDASQL" for linked server "PostGreDB" returned message "ERROR: column "PID" does not exist;
No query has been executed with that handle".
Msg 7350, Level 16, State 2, Procedure containerload2, Line 3
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PostGreDB".

Additionally i cannot insert data

---------------------------
Microsoft SQL Server Management Studio Express
---------------------------
No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: The operation could not be performed because OLE DB provider "MSDASQL" for linked server "PostGreDB" was unable to begin a distributed transaction.

OLE DB provider "MSDASQL" for linked server "PostGreDB" returned message "MSDTC XARMCreate error".

Correct the errors and retry or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------

#2.1.1 Wolfgang on 2009-10-23 04:26 (Reply)

Wolfgang,
Save yourself some hassle and don't use mixed or upper case field/table names in PostgreSQL. Change them to all lowercase.

If you use upper case letters then, you need to quote the field names as well as what you are currently doing with the table name. In your example since PID is not quoted -- it will be looking for a field called "pid" and not "PID"

#2.1.1.1 Regina on 2009-10-23 07:31 (Reply)

Hello.
Your article is very Worthy. I still have the following trouble:

1. I can not see the tables of Postgres in the Object Explorer. The following messages pops up:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "NNMSERVERLINKED". (Microsoft SQL Server, Error: 7303)

- I have done: Running the driver, creating the DSN successfully.

Thanks in advanced for your soon reply!

#3 Roxana on 2009-12-22 17:37 (Reply)

You verified you have the right user name and password and other settings. The error as I recall is not terribly helpful and always gives that error if any part is wrong.

#3.1 Regina on 2009-12-24 22:56 (Reply)

After I created the DSN, with the download from http://code.google.com/p/visionmap/downloads/detail?name=setup_psqlODBC_x64_8.3.4.0.exe&can=2&q=
I had to modify the T-SQL. Had to add a datasource name (@datasrc='PostgreSQL35W')

EXEC master.dbo.sp_addlinkedserver @server = N'NAMEOFLINKEDSERVERHERE', @srvproduct=N'PostgreSQL35W',
@datasrc='PostgreSQL35W',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=pgUser;Server=pgHostIP;database=pgDatabase;pwd=pgUserPassword'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NAMEOFLINKEDSERVERHERE',
@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

#4 JuanMa on 2010-09-23 19:55 (Reply)

I downloaded and installed every link on the page, where the heck is the psqlodbcwAMD64.reg file? I cannot find it anywhere therefore cannot run it

Google turns up empty, and the site that things link to is some Japanese site that you can no longer download the driver

Someone please help!

#5 Serge Mirault on 2010-10-21 02:59 (Reply)

Serge,
Haven't tried the new 64-bit driver yet, but on the psqlodbc site, there is now a 64-bit ODBC driver.

Give that one a try
http://www.postgresql.org/ftp/odbc/versions/msi/

Its the one called: psqlodbc_09_00_0101-x64.zip

http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versions/msi/psqlodbc_09_00_0101-x64.zip

#5.1 Regina on 2010-10-22 13:37 (Reply)

A few questions..
1. Step 2: Can you provide me a list of the .dll filenames to ensure i am using the correct one?

2. Step 3: I have the same problem as Serge above. I downloaded and installed the ..._0100_1-x64.zip package and am still unable to find the psqlodbcwAMD64.reg file.

Thank you in advance!

#5.2 Craig Jones on 2012-07-26 16:16 (Reply)

Craig,

The .reg file was packaged with the experimental dll we were using when we wrote this article (quite a while ago).

The ODBC drivers that are on the PostgreSQL site don't need the registration since they do it as part of the MSI install process.

Read our newer article on this:
http://www.postgresonline.com/journal/archives/196-SQL-Server-64-bit-Linked-Server-woes.html

The names of the drivers have changed over the years, so hesitant to give out a name since I think I'm running an older version so might be misleading. I think the article we have listed above has the newer driver name to reference

#5.2.1 Regina on 2012-07-27 08:15 (Reply)

Linked Server fails from SQL Server 2008 R2 and PostgreSQL

Please let me know the solution for following error!!

I created a SYSTEM DSN (ODBC) onto PostgreSQL Server from Windows Server 2008 (64-bit) on which the SQL Server 2008 R2.

Then I created a Linked Server by using the SQL Server management studio GUI, and the test connection was succeeded.

I can see all table names of PostgreSQL on the SQL Server management studio GUI, however, if I try to generate a SQL script, the following error occurs.

--------------------------------------------------------------------------------
Enumerate columns failed for LinkedServer 'POSTGRESQL35W'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)".
Conversion failed because the data value overflowed the data type used by the provider.
(Microsoft SQL Server, Error: 7346)
--------------------------------------------------------------------------------

Also, if I try to use OpenQuery;
select * from openquery([POSTGRESQL35W],'select * from public.t_event');
the following error occurs.

--------------------------------------------------------------------------------
The OLE DB provider "MSDASQL" for linked server "POSTGRESQL35W" reported an error.
The provider reported an unexpected catastrophic failure.
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "POSTGRESQL35W".
--------------------------------------------------------------------------------

#6 Amy on 2011-04-18 01:35 (Reply)

I think there are some data types that don't translate well via the driver. For those I usually have to case them as part of the sql statement.

To see if that is the issue, try selecting a couple of columns that are simple like just varchar and integer.

#6.1 Regina on 2011-04-18 11:36 (Reply)

Amy,

forgot about something try using MSDASQL.1 instead refer to this:

http://www.postgresonline.com/journal/archives/196-SQL-Server-64-bit-Linked-Server-woes.html

#6.2 Regina on 2011-04-18 22:56 (Reply)

Thank you for useful information. quite worked for me

#7 ofis dersi (Homepage) on 2011-08-21 10:33 (Reply)

Hi All I am stuck with a problem. I want to access MS Sql remotely from postgres (on my local m/c) which is installed on Solaris 10 i386. This is to retrieve some of the columns from a table.

#8 Soniya on 2012-08-06 08:36 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: Phone* What is six plus zero?
	Remember Information? Subscribe to this entry

Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting

Friday, April 25. 2008

Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

Postgres OnLine Journal PostGIS in Action About the Authors Consulting

Friday, April 25. 2008

Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting