Post reply

Linked Server Problems from changed table

  • February 1, 2005 at 12:28 pm

    #86703

    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

    #540018

    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

    #540032

    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

    #540180

    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

    #540228

    Great!  Thanks.  RH

  • March 16, 2005 at 1:04 pm

    #547357

    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