PostgreSQL (PostODBC) and MS SQL Server 7

Has anyone tried setting up Postgres as a linked server under Microsofts SQL
Server 7 (Beta 3 is the latest I have)?

I am able to create the link correctly (see below) and see all the tables
available in Postgres, but if I try querying anything in them I get the
following error

Server: Msg 7313, Level 16, State 1
Invalid schema or catalog specified for provider 'MSDASQL'.


SQL Server 7 uses OLE DB as the only mechanism to access external data
sources (a similar concept to linked tables in Access).  As such, the OLE DB
Provider for ODBC (identified by the MSDASQL above) is required to access
any ODBC System DSN's.  Once that is setup, a query (eg the simple SELECT
below) _should_ be able to be performed using the following

SELECT * FROM LinkedServerName.Catalog.Schema.TableName;

Unfortunately, this is where it falls over.  In most cases, you should be
able to use LinkedServerName...TableName (ie. no catalog or schema is
required), but this doesn't work.  Experimenting with various combinations
has revealed that the schema field cannot be specified (It results in the
error "Illegal 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").  Any value I put in for the
catalog however gives me the annoying error above...

Any ideas???  (What needs to be supplied in the catalog field, etc?)


Regards
David Eagles



For those interested, I had to perform the following steps to get the link
setup

1. Create an ODBC System DSN that points to your Postgres server
2. Within the SQL Server Enterprise Manager, create the link by specifying a
provider name of MSDASQL (the OLE DB to ODBC Provider).  SQL Server only
supports OLE DB providers directly, so the special MSDASQL gateway provider
is required to use ODBC datasources.  The Data Source needs to be set to the
name of the System DSN you created.  ALSO, you MUST enable the option "All
local logins connect to the linked server without using their own user
credentials" checkbox under the Security tab.
3. If all this is done ok, you should have a new server listed with all the
tables visible under the "Tables" branch of the tree.
4. Now query it using the 4 part name shown above - if it works, please let
me know!!!!!