SQL Server Performance

Linked server problem

Discussion in 'SQL Server DTS-Related Questions' started by matey, May 9, 2003.

  1. matey New Member

    Trying to copy live data from live SQL server to a development server. Tried DTS and simple Insert/Select statement. But having problems with one of the tables.

    There seems to be a simple linked server meta data problem. For example on running

    select top 10 * from live SQL server.database.dbo.tablename

    on development server, I get the error.....

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

    But other tables link ok.

    Can someone please help
    Thanks in Advance.
  2. satya Moderator

    What is the Service pack level on the SQL Server?

    _________
    Satya SKJ
  3. matey New Member

  4. satya Moderator

    Search for instcat.sql file from SP3 and run against the server to fix the problem.

    _________
    Satya SKJ
  5. satya Moderator

    Run agaiainst master database.

    _________
    Satya SKJ
  6. matey New Member

    still having the same error with this particular table even after running the script against the server.
  7. satya Moderator

  8. Argyle New Member

    Yes refer to the article satya mentioned above. I have gotten this exact same error when transfering data from sql 7 to sql 2000. You need to re-arange he internal representation of the column orders on the source table.

    /Argyle
  9. matey New Member

    or how do i delete a tables with foreign key references
  10. satya Moderator

    Use the ALTER TABLE command to temporarily disable FOREIGN KEYs.
    However, SQL Server never lets you truncate a table's FOREIGN KEY constraints reference, even if you use NOCHECK to disable that constraint.




    _________
    Satya SKJ
  11. gaurav_bindlish New Member

    This problem comes when you change the table structure of one of the table that is involved in the query after making the SQL server as a linked server. I had faced the same problem before. The solution to this problem is use OPENQUERY for querying the data. If you use this, no matter how many times you change the underlying structure of table, the error will not come on screen. The other colution can be to drop and recreate the table if possible.

    Now that you have faced this problem, can you check if server option "lazy schema validation" makes any difference to this. My assumption is that setting this option FALSE should solve the problem as well.

    HTH.

    Gaurav

Share This Page