SQL 2005 server that has a SQL 2000 linked server Synonym Problem

I have the following setup:  A SQL 2005 server has a linked server that is SQL 2000.  The SQL 2005 server has a synonym that references the linked server.  Periodically, the connection between the servers will fail unexpectedly with the following message from the 2005 server -
 
The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time ("A") and run time ("B") for table ""database"."schema"."tablename"".

To fix the connection, I am required to re-create the linked server on the SQL 2005 machine and then re-create the synonym as well.

To the best of my knowledge, no changes are being made to the tables or schemas that use or are referenced by this link.  I have been unable to determine the cause for the connection failure.  What can I do to prevent it from happening moving forward?


The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time ("A") and run time ("B") for table ""database"."schema"."tablename"".

Open in new window

force3incAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
what service pack for sqlserver 2005 do you have?
it needs to be at least 1
----
where did you get direction to recreate linked server? You do not need recreate linked server.
What provider did you select in your linked server for connection to sql server 2000?
What OS 32- or 64 bit on the box running sql server 2000?
------------------------------------------------------------------------------
< Periodically, the connection between the servers will fail unexpectedly with the following message from the 2005 server>
Pls clarify:
what are you running at time of failure?
where did you see the error?
what are you running on sql server 2000 during error?
Do you run reindex job on sql server 2000?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
force3incAuthor Commented:
1. We are running service pack 1 for sql server 2005.
2. Next time I will try it without recreating the linked server.
3. We selected the Microsoft OLE DB Provider for SQL Server.
4. We are running a 32-bit OS on the box running SQL Server 2000.
5. At the time of failure, we were running an ASP.NET Application.
6. We saw the error when connecting to SQL Server 2000.
7. No, we have not run a re-index job on SQL Server 2000 but we may try this in the future.
0
force3incAuthor Commented:
I tried recreating the synonym without recreating the linked server and it did work.  In addition, we did find find a reindexing job.
0
force3incAuthor Commented:
EugeneZ's response led us to the correct solution indirectly.  Our problem was caused by automatically running a reindex job on SQL Server 2000.  After stopping the automatic reindex job, the linked server problem ceased.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.