PostgreSQL Linked Server on SQL Server 2005 64-bit

Sorry for the cross-post here. I think the issue of being able to connect
to PostgreSQL from a 64-bit Windows system and in particular 64-bit SQL
Server 2005 has come up a couple of times on both PostGIS and PostgreSQL
ODBC groups. Jeff Crumbley kindly notified us that Microsoft released this
month a 64-bit OLEDB for ODBC driver which now makes connecting from a 64-it
SQL Server install via a Linked Server configuration possible. We thank him
greatly for that and have our linked servers working now.

We have posted the steps we took to achieve this on an Intel 64-bit SQL
Server 2005 install. So far seems to work well for us. We hope this
information is valuable to others.

http://www.postgresonline.com/journal/index.php?/archives/46 -Setting-up-Post
greSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.htm l

Thanks,
Leo and Regina

Re: PostgreSQL Linked Server on SQL Server 2005 64-bit

--===============1467735819==
Content-Type: multipart/alternative;
boundary="----=_Part_201_4078512.1209186016112"

------=_Part_201_4078512.1209186016112
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have doubt about performance and scalability of this approach. Correct me
if I'm wrong but native PostgreSQL OLE DB Provider would have done work more
efficiently (see http://www.postgresql.org/about/news.895 - version 1.2.8
supports Linked Servers).

On Fri, Apr 25, 2008 at 6:02 PM, Paragon Corporation <
lr@paragoncorporation.com> wrote:

>
> Sorry for the cross-post here. I think the issue of being able to connect
> to PostgreSQL from a 64-bit Windows system and in particular 64-bit SQL
> Server 2005 has come up a couple of times on both PostGIS and PostgreSQL
> ODBC groups. Jeff Crumbley kindly notified us that Microsoft released
> this
> month a 64-bit OLEDB for ODBC driver which now makes connecting from a
> 64-it
> SQL Server install via a Linked Server configuration possible. We thank
> him
> greatly for that and have our linked servers working now.
>
> We have posted the steps we took to achieve this on an Intel 64-bit SQL
> Server 2005 install. So far seems to work well for us. We hope this
> information is valuable to others.
>
>
> http://www.postgresonline.com/journal/index.php?/archives/46 -Setting-up-Post
> greSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.htm l
>
> Thanks,
> Leo and Regina
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-user s
>

------=_Part_201_4078512.1209186016112
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have doubt about performance and scalability of this approach. Correct me if I'm wrong but native PostgreSQL OLE DB Provider would have done work more efficiently (see  - version 1.2.8 supports Linked Servers).

RE: PostgreSQL Linked Server on SQL Server 2005 64-bit

This is a multi-part message in MIME format.

--===============1271532918==
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_000B_01C8A756.4A0B74F0"

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01C8A756.4A0B74F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Konstantin,

We'll have to take a look at that. I always thought that the PostgreSQL OLE
DB provider was less tested and robust than the ODBC one, but I have to
admit its been a while since we have looked at it. Last I tried it which
was awhile ago, I couldn't get it to go in a linked server configuration or
for SSIS processing. Things have probably changed as you mention.

I'll have to revisit this.

Thanks for the suggestion.

Regina

_____

From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of
Konstantin Izmailov
Sent: Saturday, April 26, 2008 1:00 AM
To: PostGIS Users Discussion
Cc: pgsql-odbc@postgresql.org
Subject: Re: [postgis-users] PostgreSQL Linked Server on SQL Server 2005
64-bit


I have doubt about performance and scalability of this approach. Correct me
if I'm wrong but native PostgreSQL OLE DB Provider would have done work more
efficiently (see http://www.postgresql.org/about/news.895 - version 1.2.8
supports Linked Servers).


On Fri, Apr 25, 2008 at 6:02 PM, Paragon Corporation
wrote:



Sorry for the cross-post here. I think the issue of being able to connect
to PostgreSQL from a 64-bit Windows system and in particular 64-bit SQL
Server 2005 has come up a couple of times on both PostGIS and PostgreSQL
ODBC groups. Jeff Crumbley kindly notified us that Microsoft released this
month a 64-bit OLEDB for ODBC driver which now makes connecting from a 64-it
SQL Server install via a Linked Server configuration possible. We thank him
greatly for that and have our linked servers working now.

We have posted the steps we took to achieve this on an Intel 64-bit SQL
Server 2005 install. So far seems to work well for us. We hope this
information is valuable to others.

http://www.postgresonline.com/journal/index.php?/archives/46 -Setting-up-Post
tgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.ht ml>
greSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.htm l

Thanks,
Leo and Regina


_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-user s




------=_NextPart_000_000B_01C8A756.4A0B74F0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



charset=3Dus-ascii">

RE: PostgreSQL Linked Server on SQL Server 2005 64-bit

This is a multi-part message in MIME format.

--===============0983257531==
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_001C_01C8A757.8DB2AE70"

This is a multi-part message in MIME format.

------=_NextPart_000_001C_01C8A757.8DB2AE70
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

On further inspection I see the one you are pointing at is a commercial
implementation of the PostgreSQL OLE DB Provider and not the one packaged
with PostgreSQL source and binaries. We haven't tried that one at all so
can't vouch for its strength compared to using ODBC over the OLEDB Provider
for ODBC.

A really robust solution I would suspect would be a Native SQL Server .NET
provider for PostgreSQL, but I haven't figured out how all that works to see
if the NpgSQL.NET driver can be fashioned as such. I think native SQL
Server for Oracle and DB2 providers exist.

Thanks,
Regina


_____

From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of
Konstantin Izmailov
Sent: Saturday, April 26, 2008 1:00 AM
To: PostGIS Users Discussion
Cc: pgsql-odbc@postgresql.org
Subject: Re: [postgis-users] PostgreSQL Linked Server on SQL Server 2005
64-bit


I have doubt about performance and scalability of this approach. Correct me
if I'm wrong but native PostgreSQL OLE DB Provider would have done work more
efficiently (see http://www.postgresql.org/about/news.895 - version 1.2.8
supports Linked Servers).


On Fri, Apr 25, 2008 at 6:02 PM, Paragon Corporation
wrote:



Sorry for the cross-post here. I think the issue of being able to connect
to PostgreSQL from a 64-bit Windows system and in particular 64-bit SQL
Server 2005 has come up a couple of times on both PostGIS and PostgreSQL
ODBC groups. Jeff Crumbley kindly notified us that Microsoft released this
month a 64-bit OLEDB for ODBC driver which now makes connecting from a 64-it
SQL Server install via a Linked Server configuration possible. We thank him
greatly for that and have our linked servers working now.

We have posted the steps we took to achieve this on an Intel 64-bit SQL
Server 2005 install. So far seems to work well for us. We hope this
information is valuable to others.

http://www.postgresonline.com/journal/index.php?/archives/46 -Setting-up-Post
tgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.ht ml>
greSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.htm l

Thanks,
Leo and Regina


_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-user s




------=_NextPart_000_001C_01C8A757.8DB2AE70
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



charset=3Dus-ascii">