"Inconsistent metadata" error on linked server 1

[JamesLean]

I have Server2 set up as a linked server on Server1 which has been working fine for ages. However, I have just added 4 columns to a particular table on Server2 and now cannot query the table at all through the link (works fine direct from Server2). I get the following error:

Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQL OLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

I get this error trying to query any columns of the table, not just the ones I've just added. I can still query all other tables fine.

Both servers are running SQL 7. The linked server has SP4 and the main server did have SP3, although I have upgraded this to SP4 in an attempt to fix this problem.

I have also tried completely removing the linked server and re-linking it. No joy.

The only info I can find at Microsoft is this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;266337

I have tried installing the manual upgrade as advised but didn't work either.

Does anyone have any bright ideas? --James

 

[tlbroadbent]

Are you using stored procedures or views on Server1 to access the tables on Server2? If so, recompile the objects on Server1. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JamesLean]

The problem first came to light trying to execute a stored proc so I recompiled with no luck.

After that failed I tried simply querying the table through QA:

SELECT col1 FROM server2.dbname.dbo.table1

This failed with the same error, even if I specified a column which had always been there. --James

 

[JamesLean]

Does anyone have any further ideas on this problem? I still haven't fixed it. --James

 

[JamesLean]

Have now solved this problem. FYI, it is to do with the ordinal position of each column - taken from the syscolumns table.

I had dropped and recreated one of the columns and apparently SQL 7 does not reuse the ordinal position so you get gaps in the series, eg:

syscolumns
name | colorder
col1 | 1
col2 | 2
col3 | 3

If you drop and recreate col3 then you get:

name | colorder
col1 | 1
col2 | 2
col3 | 4

This is what throws the error when using the linked server. You can find more info and the script I used to fix the gaps here:

http://www.sqlservercentral.com/columnists/jtravis/changingcolumn.asp

--James

 

[tlbroadbent]

James,

Thanks for posting the solution. I'd never noticed that behavior but we didn't use linked servers as frequently with SQL 7 as we do now. I'm going to test SQL 2000 to see if the same behavior persists. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sthorpe]

James,

Which server did you run JTRAVIS's SP on?

If server2 is linked on server1; and you get the error when running queries on server1 querying tables via the link on server2.

Thanks,

Sean Thorpe

 

[JamesLean]

Sorry for the late reply Sean - have only just picked up this reply.

I ran the script on the linked server which had the updated table - server2 in your scenario.

--James

 

[LUVLES]

James,
I have read your posting and with that I'm convinced that you can help me with my problem. I have SQL2K server running on WIN2K advanced server with service packs installed. I want to link (using linked server) my SQL server to a MSACCESS2000 database on another computer but I failed. I just wonder because I have successfully created a link server to MSACCESS if the database(MSACCESS) is on local machine(MACHINE W/C SQL SERVER IS INSTALLED).
If you have any idea, please tell me.
Thanks.