Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between...


The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time

Author
Message
BeverleyM
BeverleyM
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 58
Glen Sidelnikov (9/25/2008)
There is one more scenario. It is using synonyms in SQL 2005.
In this case this is "granted by design". You need to recreate synonym if the table on which synonym was based changed.


Ah hah, this was my issue. Thank you, Glen!

I had a synonym on a table; used Red Gate's data compare to synchronize data between test & production tables which dropped indexes and recreated them... which broke the synonym. Recreating the synonym fixed this error for me.
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
I also encounter this now and then, but with not any specific regularity.

EXEC sp_RefreshView '<View that reverences a linkedserver table>' will also allow the view to be used.

Has anyone encountered this with a view that uses SchemaBinding?
None of my views that generate this message use schemabinding.

Cheers



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Anuj Soni
Anuj Soni
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 71
Hi,

If you alter the view or procedure from which you are calling remote view then it will also resolve your issue of this error.

Alter the view and you will have solution.

Also you can use "DBCC FREEPROCCACHE" at your local server to remove the cache.

Thanks,
Anuj Soni
jkoon-760461
jkoon-760461
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 360
Can anyone explan why an index rebuild causes this error? An index rebuild is not a "schema change".
Ramdas Baghel
Ramdas Baghel
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 964
Issue : Error "reported a change in schema version between compile time" when queering LinkServer (Remote table).

Solution : Please set LinkServer property "Lazy Schema Validation" to FALSE

If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema.

If this option is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table has changed between query compilation and execution.

http://msdn.microsoft.com/en-us/library/ms190918.aspx

Ram
MSSQL DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search