linked server: certain tables giving problem: An extra colum

[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

[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

[shoubs]

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

[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: www.pgoledb.com/downloads/PGNP32e-1.3.0.2110.exe

[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 , but not all rows: 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

[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

[Moderator]

Right, 100 rows is a limitation of the trial version.

MS has fixed the issue in SQL Server 2010.

See some details here: http://social.msdn.microsoft.com/Forums ... 37f02f60fa.

I have not seen the publicly downloadable hotfix for SQL Server yet, but they said that it is almost ready.

[Moderator]

Since a related bug was fixed in PGNP provider (build 2110), MS aims for avoiding creation of hotfixes for SQLServer2005/2008.

Their reasoning is that the original issue (inconsistent metadata) is not reproducible. However, a similar issue still exists and it is related to a bug in metadata cache in all versions of SQL Server.

To reproduce the SQL Server's bug delete last column in a table in the linked server. Then next query will return error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT "Tbl1002"."id" "Col1004","Tbl1002"."lname" "Col1005" FROM "postgres"."public"."testtbl" "Tbl1002"" for execution against OLE DB provider "PGNP" for linked server "PGNP_SAMPLES".

The issue can be remedied by restarting SQLServer, while original issue could be resolved by removing the table only.

I'm trying to convince MS that hotfixes for SQLServer 2005/2008 are still needed. Please let us know on forum or via e-mail if you need the hotfix.

[bpoole]

I am having this exact problem. Occasionally I will have the need to alter a table to accommodate new data fields that i am pushing from MSSQL to Postgres. As soon as I make a schema change on the Postgres server that table becomes unusable from the MSSQL server.

I get an error in this syntax:
The OLE DB provider "PGNP" for linked server "SERVERNAME" supplied inconsistent metadata. The object "four.part.identifier.tablename" was missing the expected column "ColumnName".

If I use openquery i get this:
OLE DB provider "PGNP" for linked server "SERVERNAME" returned message "ERROR: bind message has 8 result formats but query has 9 columns".
OLE DB provider "PGNP" for linked server "SERVERNAME" returned message "Undefined column name ColumnName".

The only resolution I have is to restart the services on the MSSQL server. Unfortunately, I can only do that overnight, which means any request for schema changes result in at least a 24 hour delivery time.

I am using MSSQL server 2008 R2. I have not seen anything in the cumulative update packages that expressly reference this issue. And I am hesitant to simply throw hot fixes at the problem.

Has anyone been able to resolve the issue without restarting the MSSQL services or heard anything from Microsoft about a hotfix for 2008?

[Moderator]

This particular issue may be caused by obsolete metadata cache in the PGNP Provider. Fortunately, you can resolve it without restarting SQL Server. To recycle PGNP cache execute following command in SSMS after a table altered in Postgres:

Code: Select all

EXEC('pgnp_refreshmetadata('''',''test'')') AT PGNP_SAMPLE

Fro more information about pgnp_refreshmetadata read the Developers Manual (available from pgoledb.com).