linked server: certain tables giving problem: An extra colum

More
9 years 9 months ago #174 by lucD
lucD created the topic: linked server: certain tables giving problem: An extra colum
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
9 years 9 months ago #175 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

More
9 years 9 months ago #177 by shoubs
shoubs replied the topic: Re: linked server: certain tables giving problem: An extra colum
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
9 years 9 months ago #178 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

More
9 years 9 months ago #179 by lucD
lucD replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

More
9 years 9 months ago #180 by lucD
lucD replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

More
9 years 9 months ago #181 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

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

More
9 years 6 months ago #227 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
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.

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

More
8 years 4 months ago #1317 by bpoole
bpoole replied the topic: Re: linked server: certain tables giving problem: An extra colum
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?

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

More
8 years 4 months ago #1329 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
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:
EXEC('pgnp_refreshmetadata('''',''test'')') AT PGNP_SAMPLE

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

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

More
8 years 3 months ago #1402 by bpoole
bpoole replied the topic: Re: linked server: certain tables giving problem: An extra colum
I had opportunity to review and test the Refresh Metadata Cache.

IN running the command I receive the following error:

"ERROR: cannot execute NOTIFY during recovery"

The metadata does not refresh.

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

More
8 years 3 months ago #1447 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra colum
Another way to recycle Provider's cache is to close application(s) that use the provider.

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

More
7 years 7 months ago #4399 by gregg.erbaugh@sfsltd.com
gregg.erbaugh@sfsltd.com replied the topic: Re: linked server: certain tables giving problem: An extra c
I'm getting this same/similar error: "ERROR: bind message has 7 result formats but query has 6 columns " whenever I reboot my server. I've tried executing the EXEC('pgnp_refreshmetadata('''',''remotetablename'')') AT REMOTESERVER ; on my SQL Server 2008 box, but it has no effect.

As inferred, I'm running Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64), the PGOLEDB driver is a 64-bit, v1.3.0.2257 and the PostGres server is PostgreSQL 8.3.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20081105 (Red Hat 4.3.2-7).

Most of the time, the error would almost cure itself and we're not sure why. Whether it was running the queries manually through a query analyzer or dropping and re-adding the linked server or some other random act. Currently we're experiencing random failures where one time a query runs and it works, other times it runs and generates the aforementioned error.

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

More
7 years 7 months ago #4419 by Moderator
Moderator replied the topic: Re: linked server: certain tables giving problem: An extra c
Does this error happen after a table schema change? How often it happens?

Please send more details to support.

Thank you!

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

Time to create page: 0.084 seconds
Powered by Kunena Forum