Re: linking from SQL Server - Mailing list pgsql-sql
From | Oleg Lebedev |
---|---|
Subject | Re: linking from SQL Server |
Date | |
Msg-id | 3C584F6E.9A0C310F@waterford.org Whole thread Raw |
In response to | linking from SQL Server (Oleg Lebedev <olebedev@waterford.org>) |
List | pgsql-sql |
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 > >