linked server: certain tables giving problem: An extra colum

More
14 years 4 months ago #181 by Moderator
Right, 100 rows is a limitation of the trial version.

MS has fixed the issue in SQL Server 2010.

See some details here: 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
14 years 2 months ago #227 by 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.

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

  • Visitor
  • Visitor
12 years 11 months ago #1317 by
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
12 years 11 months ago #1329 by 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:
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.

  • Visitor
  • Visitor
12 years 11 months ago #1402 by
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
12 years 11 months ago #1447 by Moderator
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.

Time to create page: 0.160 seconds
Powered by Kunena Forum