Thread: 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
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 >
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
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
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
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
> >