February 1, 2005 at 12:28 pm
I made a very simple change to a table on a linked server, and now the main server can't read it. I keep getting the error:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
OLE DB error trace [Non-interface error: OLE DB provider returned an extra column at execution time: ProviderName='SQLOLEDB', ColumnName='TenantName', Rowset="hiwrptdata"."dbo"."RealmStageSuites"].
Any ideas on how to get around this. I have already dropped the linked server and re-added it but that didn't fix it.
Thanks
Ryan
February 1, 2005 at 12:57 pm
Well - I guess it's a timing thing. The problem cleared up about 10 to 15 minutes after a re-linked the server. Maybe this would have cleared up over time even without the delete and re-link???
Anyone know for sure what happend?
Thanks. RH
February 1, 2005 at 2:29 pm
Query Cache had something in it until SQL Server decided to recompile the Query.
I know there is a way to flush the whole cache, but that wouldn't seem to be a good thing in this case.
I know there is a WITH RECOMPILE for SPs, but what about a query ??
KlK
February 2, 2005 at 9:04 am
This one's all too familiar. in the end we did some googling and found the following (due credit to the original author, James Travis).
All of our databases now have this proc, and any time we change the definition of a table which is used across linked servers, running this seems to prevent the 'inconsistent metadata' message.
Alter PROCEDURE procRefreshTableMetadata
@table sysname
AS
DECLARE @SQLState VARCHAR(2000)
--Configure server to allow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
/*Build string to update object, the only reason I build a string is the allow updates exec does not allow straight SQL to occurr.*/
SET @SQLState = 'UPDATE
syscolumns
SET
colid = TruePos,
colorder = TruePos
FROM
syscolumns
INNER JOIN
(SELECT
[name],[id],
colorder, (SELECT COUNT(*) + 1 FROM syscolumns ic WHERE ic.colorder < c.colorder AND ic.[id] = c.[id]) as TruePos
FROM syscolumns c WHERE [id] = OBJECT_ID(''' + @table + ''')
) AS CalcVals ON syscolumns.[name] = CalcVals.[name] AND syscolumns.[id] = CalcVals.[id] AND
syscolumns.colorder = CalcVals.colorder'
EXEC (@SQLState)
--Configure server to disallow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
February 2, 2005 at 11:36 am
Great! Thanks. RH
March 16, 2005 at 1:04 pm
Was looking for the same solution. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply