Re: PostgreSQL / ODBC / Ms-SQL 2000

-----Original Message-----
From: "Thomas Knoop"<Th(dot)Knoop(at)Talisma(dot)nl>
Sent: 28/09/05 18:47:43
To: "pgsql-odbc(at)postgresql(dot)org"<pgsql-odbc(at)postgresql(dot)org>
Subject: Re: [ODBC] PostgreSQL / ODBC / Ms-SQL 2000

Hi Thomas,

>> 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')

The query in thhe function call is executed by the linked server so PG will restrict the resultset within the bound of the LIMIT and WHERE clauses.

Anything else is down to SQL Server...

Regards, Dave

-----Unmodified Original Message-----

Hi Dave,

First let me thank you for your input!

> > When I look at the properties of the linked server in Ms-SQL I can
> > see that the catalog column is empty. I think MS-SQL expects a
> > (default?) value here,
> > and thats why we cannot proceed creating a view.
>
> If SQL is expecting a value here even though the driver reports that
> catalogs are not supported by the database then it's broken.
> I'll see if
> I can find some time to try to work out whats going on in the week. I
> can't promise anything though.

> According the SQL docs, the correct syntax is something like:

> SELECT * FROM MYLINKEDSERVER..[public].mytable;

> However I can't get this to work. This does seem fairly common though,
> with other users complaining of the same issues with Oracle. The commonly
> suggested solution is to use:

> SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM public.mytable')

> Which works perfectly afaict, apart from a minor issue with columns of
> type 'name', for which I've just committed a fix to CVS.

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....

This looks to me as a very slow workaround.

Regards,
Thomas.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org