From: Igor Kryltsov <kryltsov@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Update PostgreSQL from MS SQL trigger
Date: Wed, 9 Mar 2005 09:44:08 +1100
Message-ID: <d0l9ss$nuk$1@news.hub.org> (raw)
References: <d0jar6$14rh$1@news.hub.org>
I managed to link PostgreSQL to MSSQL as a linked server but:
select * from [TEST].[test].[public].[users] <<<TEST - name of linked server
in MSSQL , test - Postgres db name, public - schema name, users - table
Returns:
Server: Msg 7312, Level 16, State 1, Line 7
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.
Works only via OPENQUERY:
SELECT * FROM OPENQUERY (TEST, 'select * from users');
Does anybody know how to query Postgres WITHOUT OPENQUERY.
I found in groups that:
" 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. "
Thank you,
Igor
"Igor Kryltsov" <kryltsov@yahoo.com> wrote in message
news:d0jar6$14rh$1@news.hub.org...
> Hi,
>
> I have table 'test_m'(id integer) in MSSQL and I want to write
> on_test_m_insert trigger in MS SQL which will insert value into PostgreSQL
> table 'test_p' from database 'test_db' running on host '10.3.2.5'.
>
> Can this be achieved with PostgreSQL ODBC driver? If yes, please post
> template of such trigger.
>
>
> Thank you,
>
>
> Igor
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kryltsov@yahoo.com
Subject: Re: Update PostgreSQL from MS SQL trigger
In-Reply-To: <d0l9ss$nuk$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox