Linked Server query fails due to inconsistent metadata

Executing a SQL query against an OpenEdge database via a Microsoft SQL Server Linked Server fails due to inconsistent metadata, reporting that "An extra column was supplied during execution that was not found at compile time."

May 22, 2019
Knowledge

Title

URL Name

Linked-Server-query-fails-due-to-inconsistent-metadata

Article Number

000113285

Environment

Product: OpenEdge Version: All supported versions OS: Windows Other: Microsoft SQL Server, Linked Server

Question/Problem Description

Linked Server query fails due to inconsistent metadata.

Executing a SQL query against an OpenEdge database via a Microsoft SQL Server Linked Server fails due to inconsistent metadata.

Error reports that "An extra column was supplied during execution that was not found at compile time."

Error message does not specify which column is "extra".

Schema of the underlying OpenEdge table has recently changed.

Failure or success of the query depends on the syntax used:

Executing the query via the Linked Server fails:
- SELECT * FROM [testdb].[testdb].[pub].[testtable]
Executing the query using OPENQUERY syntax succeeds:
- SELECT * FROM OPENQUERY([testdb], 'select * from pub.testtable')

Dropping and recreating the Linked Server has no effect.

Enabling Lazy Schema Validation on the Linked Server has no effect.

Forcing a recompile of the query has no effect (SELECT * FROM [testdb].[testdb].[pub].[testtable] OPTION RECOMPILE).

Executing the equivalent query from an alternative SQL client (SQL Explorer, WinSQL, etc.) succeeds.

Steps to Reproduce

1) Create an OpenEdge database. 2) Add a table with 5 fields. Optionally, add sample data. 3) Start the database in client-server mode (-H -S) 4) Create an ODBC DSN for the database. 5) In SQL Server, create a Linked Server for the database. 6) Execute queries similar to the following in SQL Server: SELECT * FROM [testdb].[testdb].[pub].[testtable] SELECT * FROM OPENQUERY([testdb], 'select * from pub.testtable') Both should return data. 7) In the OpenEdge database, delete the 4th field. Re-run the queries in SQL Server. The first should fail with the error; the second should succeed. 8) In the OpenEdge database, delete the 5th field. Re-run the queries in SQL Server. Both queries should return data again. 9) In the OpenEdge database, add 2 new fields. Run the queries in SQL Server. The first should fail with the error; the second should succeed.

Clarifying Information

Using the OPENQUERY syntax bypasses whatever processing the Linked Server performs.

The SQL query is passed through to the OpenEdge database and is executed by the OpenEdge SQL engine.

Error Message

Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "<linked server>" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

Defect Number

Enhancement Number

Cause

The exact cause is not known at this time. It would appear that Microsoft SQL Server Linked Servers cannot handle breaks in the _Field._Field-physpos value sequence for the table's fields in the OpenEdge database.
Removing fields from the end of the field list has no effect and the Linked Server query will execute normally.
Removing fields from the start or middle of the field list will result in the Linked Server error when executing the query via the Linked Server.

Resolution

Contact Microsoft Technical Support for further assistance.
Other SQL clients continue to query the table after the schema change has been committed, so the issue appears to be specific to Linked Server functionality and internals.

Workaround

Option #1
Use SQL Server's OPENQUERY syntax.
OPENQUERY passes the SQL query through to the OpenEdge SQL engine to execute/process.

Option #2
On the OpenEdge side, dump and reload the affected table(s).
This will reset the value sequence stored in _Field._Field-physpos, eliminating any gaps.

Notes

References to other documentation:

OpenEdge Data Management: Database Administration, Dumping and Loading

Progress article(s):
How to query a Progress database via a Microsoft SQL Server Linked Server?
Error running query in SQL Server via Linked Server after Progress Database schema change

Microsoft article(s):
OPENQUERY (Transact-SQL) - https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql

Keyword Phrase

Last Modified Date

11/20/2020 7:02 AM

Linked Server query fails due to inconsistent metadata

Files

Was this article helpful?

Was this article helpful?

Linked Server query fails due to inconsistent metadata

Information

Related Articles