OLE DB provider 'SQLOLEDB' supplied inconsistent metadata

[orientalsupport]

When trying to access a table in a remote server I got this error only for two tables of the database.

This is the select statement :

select * from ispserver.dbaccount.dbo.tbltransaction

OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

There are other tables in the database that ca be accessed without any problem.

Any ideas, your help is really appreciated.


Thanks in advance.

Ed.

 

[tlbroadbent]

This error can happen if someone changed a table structure on the remote server and failed to recompile views on the remote and/or local server.

Also, there is a bug documented in the following artcile.

http://support.microsoft.com/support/kb/articles/Q255/0/97.ASP

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jcrivera]

Greetings Terry.
Thanks for your assistance and comments.

We already know that if a change in the base table structure is done and the view to that table is not properly refreshed we'll get this type of metadata errors.

However, what Ed is trying to explain is different. Our situation consists that if we try to make a remote query to the view, the query runs fine (and we dropped/recreated the view to make sure the view runs ok). However, if the query is done directly to the TABLE, the query will bomb with this same Msg 7356 error.

Did I explain accurately?

Please advise.

PS: Do u suspect that this might be a bug as well?

 

[tlbroadbent]

Which version of SQL Server are you running? SQL 7 SP2 supposedly corrected this problem.

However, SQL 7 SP2 had an installation issue as documented in

http://support.microsoft.com/support/kb/articles/Q266/3/37.ASP

You might also try using OpenQuery on the local server or create a view on the remote server and select from the view.

Select * From
OpenQuery(ispserver, 'select * from dbaccount.dbo.tbltransaction') Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jcrivera]

We're using SP3.

Do u think that running this script file (instcat.sql) with SP3 would help?

Please advise.

J.C.

 

[jcrivera]

Terry et al:

We were able to recreate this problem and it is in the following way:

Let's assume this scenario. Server "Svr-A" is remote, and Server "Svr-B" is local. Both are version 7.0

(1) Logging locally in server A (either by the Query Analyzer or Ent.Manager), go into database "db-A" and alter table "tbl-A" by dropping any column of it. Save the changes.

(2) From server B run the following query:

select * from Svr-A.db-A.dbo.tbl-A

You'll get the error.

J.C.

PS: If instead you alter the table by ADDING a new column, the distributed query will run fine.