Thread: linking from SQL Server

linking from SQL Server

From
Oleg Lebedev
Date:
Hi,
I am wondering if anybody tried to link PostgreSQL tables/databases from
SQL Server.
As I understand SQL Server uses OLE DB for this kind of connections. I
wonder if this is possible at all with postgres.
thanks,

Oleg



Re: linking from SQL Server

From
"Steve Boyle \(Roselink\)"
Date:
Oleg,

I've just spent the last couple of hours trying to get this to work - with
little success.

I managed to create the linked server using both Enterprise manager and also
Query Analyser using

EXEC sp_dropserver 'Postgresql'
EXEC sp_addlinkedserver 'Postgresql', 'Postgresql', 'MSDASQL',
'DATABASE=[dbname];SERVER=localhost;PORT=5432;A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=
0;A6=;A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;
B9=0;C0=0;C1=0;C2=dd_'

I generated the DSN above from a linked Access table (I've replaced my
actual database name with [dbname] and it works when I test the connection
string in Access.

I tested the connection with:

select * from postgresql...test
select * from postgresql..test
select * from postgresql.test

(The documentation suggests that it wants a fully qualified table name so I
tried all of the above in case there was some mapping taking place in any of
the drivers).

This reports that the linked server has been created but I cannot run SQL
Server queries against it, and cannot find where I can get more details
relating to the OLE DB errors produced.

If anyone can get any further with pls post as I think it would open up some
interesting possibilities.

sb

----- Original Message -----
From: "Oleg Lebedev" <olebedev@waterford.org>
To: "Postgres SQL Mailing List" <pgsql-sql@postgresql.org>
Sent: Thursday, January 24, 2002 9:13 PM
Subject: [SQL] linking from SQL Server


> Hi,
> I am wondering if anybody tried to link PostgreSQL tables/databases from
> SQL Server.
> As I understand SQL Server uses OLE DB for this kind of connections. I
> wonder if this is possible at all with postgres.
> thanks,
>
> Oleg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>



Re: linking from SQL Server

From
"Josh Berkus"
Date:
Oleg,

> I am wondering if anybody tried to link PostgreSQL tables/databases
>  from
> SQL Server.
> As I understand SQL Server uses OLE DB for this kind of connections.
>  I
> wonder if this is possible at all with postgres.

In theory, yes.  In practice, no.

According to the documentation, SQL-Server supports any Win32 ODBC
 driver ... which would include PostgresODBC.  However, in practice,
 only Microsoft-supplied drivers actually work; ODBC drivers for
 Postgres and MySQL mysteriously do not function with SQL-Server (even
 though they do work with MS Access).

I think this is incompetence on Microsoft's part rather than malice;
 all of this works through DTS, a utility which is responsible for over
 300 Knowledge Base issues.

Note:  I have not tried any of this with SQL Server 2000, just 7.0 sp3.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco    and non-profitorganizations.      San Francisco


Re: linking from SQL Server

From
"SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
I have access to a SQL 2000 server if you can give me some detailed
instructions I'd be happy to test it out for you.  I'm also interested in
seeing if this will work.

Thanks,
Mike

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, January 24, 2002 6:19 PM
To: Oleg Lebedev; Postgres SQL Mailing List
Subject: Re: [SQL] linking from SQL Server


Oleg,

> I am wondering if anybody tried to link PostgreSQL tables/databases
>  from
> SQL Server.
> As I understand SQL Server uses OLE DB for this kind of connections.
>  I
> wonder if this is possible at all with postgres.

In theory, yes.  In practice, no.

According to the documentation, SQL-Server supports any Win32 ODBC
 driver ... which would include PostgresODBC.  However, in practice,
 only Microsoft-supplied drivers actually work; ODBC drivers for
 Postgres and MySQL mysteriously do not function with SQL-Server (even
 though they do work with MS Access).

I think this is incompetence on Microsoft's part rather than malice;
 all of this works through DTS, a utility which is responsible for over
 300 Knowledge Base issues.

Note:  I have not tried any of this with SQL Server 2000, just 7.0 sp3.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco    and non-profitorganizations.      San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Re: linking from SQL Server

From
"Josh Berkus"
Date:
Mike,

> I have access to a SQL 2000 server if you can give me some detailed
> instructions I'd be happy to test it out for you.  I'm also
>  interested in
> seeing if this will work.

1. Get the PostgresODBC server from PgAdmin(http://pgadmin.postgresql.org/)
2. Install it and set up an ODBC source pointing to a PostgreSQL server(you have one, yes?)
3. Use Data Transformation Service to: a) Import data from the Postgres Server into SQL Server b) Export data from SQL
Serverto PostgreSQL.
 
4. Tell us what works.

Anything else, Oleg?

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: linking from SQL Server

From
Oleg Lebedev
Date:
I got OPENQUERY to work with postgres database linked from SQL Server.
The following works fine:
SELECT activitytypename FROM
OPENQUERY ('webspec', 'select * from activitytype');

However, when I try to do smth like:
SELECT activitytypename FROM webspec.webspec..activitytype;
SELECT activitytypename FROM webspec.postgres.webspec.activitytype;
SELECT activitytypename FROM webspec.pg_database.webspec.activitytype;
I get the same error:
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A      four-part name was supplied, but the
providerdoes not expose the      necessary interfaces to use a catalog and/or schema.
 

Can anyone tell me if postgres supports catalogs and schemas similar to SQL
Server. How can I find out this information for my database?
thanks,

Oleg

"Steve Boyle (Roselink)" wrote:

> Oleg,
>
> I've just spent the last couple of hours trying to get this to work - with
> little success.
>
> I managed to create the linked server using both Enterprise manager and also
> Query Analyser using
>
> EXEC sp_dropserver 'Postgresql'
> EXEC sp_addlinkedserver 'Postgresql', 'Postgresql', 'MSDASQL',
> 'DATABASE=[dbname];SERVER=localhost;PORT=5432;A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=
> 0;A6=;A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;
> B9=0;C0=0;C1=0;C2=dd_'
>
> I generated the DSN above from a linked Access table (I've replaced my
> actual database name with [dbname] and it works when I test the connection
> string in Access.
>
> I tested the connection with:
>
> select * from postgresql...test
> select * from postgresql..test
> select * from postgresql.test
>
> (The documentation suggests that it wants a fully qualified table name so I
> tried all of the above in case there was some mapping taking place in any of
> the drivers).
>
> This reports that the linked server has been created but I cannot run SQL
> Server queries against it, and cannot find where I can get more details
> relating to the OLE DB errors produced.
>
> If anyone can get any further with pls post as I think it would open up some
> interesting possibilities.
>
> sb
>
> ----- Original Message -----
> From: "Oleg Lebedev" <olebedev@waterford.org>
> To: "Postgres SQL Mailing List" <pgsql-sql@postgresql.org>
> Sent: Thursday, January 24, 2002 9:13 PM
> Subject: [SQL] linking from SQL Server
>
> > Hi,
> > I am wondering if anybody tried to link PostgreSQL tables/databases from
> > SQL Server.
> > As I understand SQL Server uses OLE DB for this kind of connections. I
> > wonder if this is possible at all with postgres.
> > thanks,
> >
> > Oleg
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >