Re: PostgreSQL / ODBC / Ms-SQL 2000

> -----Original Message-----
> From: pgsql-odbc-owner(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Thomas Knoop
> Sent: 29 September 2005 17:26
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] PostgreSQL / ODBC / Ms-SQL 2000
>
> >>> SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM
> >>> public.mytable')
>
> >> As far I can see this tries to open the table complete,
> and fails in
> >> my case cause i have a ADSL connection and it tries to
> fetch all the
> records....
>
> > So modify the query as required...
>
> > SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM
> > public.mytable WHERE foo < 10 LIMIT 5')
>
> Well this does not work either, remember i'm creating a view....

CREATE VIEW foo AS SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT *
FROM public.mytable WHERE foo < 10 LIMIT 5')

> Let me give some background: I'm using the PostgreSQL as a
> datasource from
> an application that only connects to MS-SQL.
>
> So either I create a view in MS-SQL or I talk to a linked
> server in MS-SQL
> directly.
>
> But both don't work in the current version.
>
> - In the linked server setup i need to supply a catalog (of
> which the driver
> does not seem to support)

As I already pointed out, the correct syntax is SERVER..schema.table.
However, SQL Server still doesn't like doing this with psqlODBC, as
others on the net have found it also doesn't work with Oracle.

> - In the view it also complains about the catalog that is not
> supplied...
>
> Would it be strange to suggest that we use a 'dummy' catalog
> in the ODBC
> driver for MS-SQL?

That won't work because the driver would then report the servers
capabilities incorrectly to applications which may then generate invalid
queries.

Regards, Dave