linked server: certain tables giving problem: An extra colum

  • lucD
  • Topic Author
  • Visitor
  • Visitor
14 years 5 months ago #174 by lucD
Hello,

I'm using sqlServer2005 standard edition, i have PGNP installed and i made a linked server to a postgreSQL-database. I can read most of the tables, but queries on certain tables give this error:

The OLE DB provider "PGNP" for linked server "MYLINKEDSERVERNAME" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

I red it could be caused by a change in the schema - though i can hardly imagine the schema being changed within seconds after installing (but, fair enough, i have no vison on what the administrator of the postgreSql-database was doing at that time).

So after this occured, i added a second linked server with the same connection, but it gives the same problem. I tried to set the server option "lazy schema validation" but this was rejected (not supported for this version of sqlserver).

Any clue on how to proceed?

Thank you!

Luc

Please Log in or Create an account to join the conversation.

More
14 years 5 months ago #175 by Moderator
Luc,
Do you use 4-part identifier when querying the table (e.g. select * from mylinkedserver.db.sch.tbl)? Would you try openquery syntax instead (e.g. select * from openquery(mylinkedserver, 'select * from tbl'))?

If in either case it returns the same error, would you send us the table schema (for the table that returns the error)?

I spoke to a MS developer. He said that the "inconsistent metadata" issue is caused by a defect in SQL Server schema cache. As a workaround "openquery" might be used. However, openquery have some limitations. Btw, MS is working on the fix now. We found that the only way to return table into working condition is to drop it in Postgres, perform a query via linked server to clear cache, then recreate the table in postgres.

Konstantin

Please Log in or Create an account to join the conversation.

  • Visitor
  • Visitor
14 years 4 months ago #177 by
When I did a similar thing with an AS400 I used to create table variables for the subset of data i required from each table then joined the tbl vars together. Produced a much more reliable result, and in some cases a temp table instead of tbl vars might be quicker. Theres lots of theory on this if you google it...

Please Log in or Create an account to join the conversation.

More
14 years 4 months ago #178 by Moderator
I have got more info about the issue from MS. First, they are going to release a hotfix for SQL Server to address the issue in internal metadata cache. Second, they found an issue in PGNP provider that we have fixed today.

Please use build 2110 or later with the fix: <!-- w --> www.pgoledb.com/downloads/PGNP32e-1.3.0.2110.exe <!-- w -->

Please Log in or Create an account to join the conversation.

  • lucD
  • Topic Author
  • Visitor
  • Visitor
14 years 4 months ago #179 by lucD
Konstantin,

Sorry I didn't reply sooner.

Thank you very much for the new build! Installed it and here my results (in answer to your previous post of 12 november)

* previous build - 4-part -identifier: the inconsistent metadata-error
* previous build - via openquery: all columns return good (aha!), but not all rows: limited to the first 101 rows

* new build - 4-part-identifier: all columns return good :D , but not all rows: <!-- s:? --><img src="{SMILIES_PATH}/icon_e_confused.gif" alt=":?" title="Confused" /><!-- s:? --> limited to the first 101 rows
* new build - via openquery: all columns return good, but not all rows: limited to the first 101 rows

In the mean time I use an odbc-driver (but this only works with openquery) to acces the postgres-database (and i obtain all rows).

I would like to use pgnp whithout openquery of course - the fact the number of rows are limited to 101.... (and this is so with all tables in the db) : sqlserver-related? pgnp-related? postgres-related?

Thank you,
Luc

Please Log in or Create an account to join the conversation.

  • lucD
  • Topic Author
  • Visitor
  • Visitor
14 years 4 months ago #180 by lucD
Okay, i got it, the 101 rows are the limits of my trial version ;)

So it works, thanks! (any idea what the hotfix of ms will do?)

Luc

Please Log in or Create an account to join the conversation.

Time to create page: 0.192 seconds
Powered by Kunena Forum