Re: linking from SQL Server

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 provider does 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(at)waterford(dot)org>
> To: "Postgres SQL Mailing List" <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >