Thread: ODBC driver and linked server in MSSQL 2k
Hi all: I've been trying to get our MS SQL Server 2000 to interoperate with our PostgreSQL server (7.4.3 on RedHat 9, backend for Lyris LM 8.0) as a linked server via the ODBC driver. When viewing it through Enterprise Manager, it is able to display all the tables and views, but any attempt to access the tables via SQL (on the MSSQL 2k server) gives an error message: "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." I've done a bunch of googling the past couple days, and digging through the mailing list archives, and while I've found others who have encountered the same problem that I have, I haven't found anyone posting that they've found a solution. Has anyone on this list made a working linked server connection from MSSQL 2k to PostgreSQL using the official ODBC driver? Thanks Kirk
-----Original Message----- From: "Kirk Friggstad"<friggstadk@ironsolutions.com> Sent: 08/08/05 17:07:55 To: "pgsql-odbc@postgresql.org"<pgsql-odbc@postgresql.org> Subject: [ODBC] ODBC driver and linked server in MSSQL 2k > "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." I found the driver was incorrectly reporting catalog support on Friday and committed a fix for it. Can you wait for the next snapshot, or do you want an updated dll to test? (might not be until Thursday though). Regards, Dave -----Unmodified Original Message----- Hi all: I've been trying to get our MS SQL Server 2000 to interoperate with our PostgreSQL server (7.4.3 on RedHat 9, backend for Lyris LM 8.0) as a linked server via the ODBC driver. When viewing it through Enterprise Manager, it is able to display all the tables and views, but any attempt to access the tables via SQL (on the MSSQL 2k server) gives an error message: "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." I've done a bunch of googling the past couple days, and digging through the mailing list archives, and while I've found others who have encountered the same problem that I have, I haven't found anyone posting that they've found a solution. Has anyone on this list made a working linked server connection from MSSQL 2k to PostgreSQL using the official ODBC driver? Thanks Kirk ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Hi Dave: I can definitely wait for the next snapshot - this is a long-term project on our end (as in, we've been toying with the idea for a few months already and now found a bit of time to actually try it), so it's not too time-critical... yet... :-) If you happen to have an updated DLL, I wouldn't mind playing with it, but please don't go out of your way on my account. Kirk -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Tuesday, August 09, 2005 3:22 AM To: friggstadk@ironsolutions.com; pgsql-odbc@postgresql.org Subject: RE: [ODBC] ODBC driver and linked server in MSSQL 2k > "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." I found the driver was incorrectly reporting catalog support on Friday and committed a fix for it. Can you wait for the next snapshot, or do you want an updated dll to test? (might not be until Thursday though). Regards, Dave -----Unmodified Original Message----- Hi all: I've been trying to get our MS SQL Server 2000 to interoperate with our PostgreSQL server (7.4.3 on RedHat 9, backend for Lyris LM 8.0) as a linked server via the ODBC driver. When viewing it through Enterprise Manager, it is able to display all the tables and views, but any attempt to access the tables via SQL (on the MSSQL 2k server) gives an error message: "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." I've done a bunch of googling the past couple days, and digging through the mailing list archives, and while I've found others who have encountered the same problem that I have, I haven't found anyone posting that they've found a solution. Has anyone on this list made a working linked server connection from MSSQL 2k to PostgreSQL using the official ODBC driver? Thanks Kirk ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Hi,
The fix for this problem has already been released? I've tested with latest snapshot (8.1.0.5), and I'm getting the same error.
Thanks,
Sérgio
- From: "Dave Page" <dpage ( at ) vale-housing ( dot ) co ( dot ) uk>
- To: <friggstadk ( at ) ironsolutions ( dot ) com>, <pgsql-odbc ( at ) postgresql ( dot ) org>
- Subject: Re: ODBC driver and linked server in MSSQL 2k
- Date: Tue, 9 Aug 2005 10:21:38 +0100
-----Original Message----- From: "Kirk Friggstad"<friggstadk ( at ) ironsolutions ( dot ) com> Sent: 08/08/05 17:07:55 To: "pgsql-odbc ( at ) postgresql ( dot ) org"<pgsql-odbc ( at ) postgresql ( dot ) org> Subject: [ODBC] ODBC driver and linked server in MSSQL 2k > "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." I found the driver was incorrectly reporting catalog support on Friday and committed a fix for it. Can you wait for the next snapshot, or do you want an updated dll to test? (might not be until Thursday though). Regards, Dave -----Unmodified Original Message----- Hi all: I've been trying to get our MS SQL Server 2000 to interoperate with our PostgreSQL server (7.4.3 on RedHat 9, backend for Lyris LM 8.0) as a linked server via the ODBC driver. When viewing it through Enterprise Manager, it is able to display all the tables and views, but any attempt to access the tables via SQL (on the MSSQL 2k server) gives an error message: "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." I've done a bunch of googling the past couple days, and digging through the mailing list archives, and while I've found others who have encountered the same problem that I have, I haven't found anyone posting that they've found a solution. Has anyone on this list made a working linked server connection from MSSQL 2k to PostgreSQL using the official ODBC driver? Thanks Kirk
Sérgio Ramalho
Tel: +351 22 605 68 24 Fax: +351 22 605 68 10
Email: ASergio@ptinovacao.pt
PT Inovação, SA - Pólo Porto
Rua Tenente Valadim 431, 7º Piso 4100-479 Porto