Thread: Re: PostgreSQL / ODBC / Ms-SQL 2000

-----Original Message-----
From: "Thomas Knoop"<Th.Knoop@Talisma.nl>
Sent: 28/09/05 18:47:43
To: "pgsql-odbc@postgresql.org"<pgsql-odbc@postgresql.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
theLIMIT 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

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

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

Best Regards,
  Thomas.