April 30, 2012 at 9:02 am
this weekend we had an issue with one of our critical production apps running on 2005(9.00.5266.00) within this application there is a view that crosses over to a 2000 box (8.0.2187)
the view is used by a stored proc called by a SSRS report (10.50.2500)
i was several hours away from being able to get to a computer, but was able to get ahold of a dev(my emergency backup) to help, here is report:
on trying to run the report it threw an error that the metadata was out of sync.
on execution of the stored proc within management studio executed with no problems.
i had the dev connect as admin to the 2005 box and run sp_refreshview to fix metadata, and the report started working fine.
in researching this this morning, report was known to be working thurday(4/26) have a printed copy to verify.
there have been no changes made to database or code(have a rigorous change management) the last change to the underlying table was pre 5/27/2010(initial entry into source control)
view was created on 2/14/2012, no changes since
I'm at a loss for what could have caused this, has anyone ever seen similar?
pseudocode for view:
select column1_int,
column2_varchar10 COLLATE SQL_Latin1_General_CP1_CI_AS,
column3_varchar10 COLLATE SQL_Latin1_General_CP1_CI_AS,
column4_datetime,
column5_datetime,
column6_varchar2 COLLATE SQL_Latin1_General_CP1_CI_AS,
column7_varchar2 COLLATE SQL_Latin1_General_CP1_CI_AS,
column8_varchar10 COLLATE SQL_Latin1_General_CP1_CI_AS
from [sql2000server].[databasename].[dbo].[tablename]
April 30, 2012 at 9:18 am
not sure what advice to offer yet, I've got a couple of questions first:
what was the specific error that was returned? that would tell us a lot.
how is the report generated? is it a schedule SSRS report, on demand, or what?
was it called by an end user, who might not have credentials for the linked server(ie they ran the report for the first time under their credentials?)
what permissions are used for the linked server, and what would happen if the linked server was momentarily down/blocked/unavailable?
Lowell
April 30, 2012 at 9:28 am
Lowell (4/30/2012)
not sure what advice to offer yet, I've got a couple of questions first:what was the specific error that was returned? that would tell us a lot.
dev didn't get a screenshot, but this is the error he said they were getting:
The OLE DB provider "SQLNCLI" for linked server "REMOTESERVER" reported a change in schema version between compile time ("string of numbers") and run time ("string of numbers") for table ""sql2000server"."databasename"."dbo"."tablename"".
how is the report generated? is it a schedule SSRS report, on demand, or what?
on demand
was it called by an end user, who might not have credentials for the linked server(ie they ran the report for the first time under their credentials?)
errors seen by both enduser and dev(both had permissions) enduser is same one who printed report on thursday
what permissions are used for the linked server, and what would happen if the linked server was momentarily down/blocked/unavailable?
reports use a windows credential stored on the report server, reportuser has permissions to everything needed
it would return a could not connect error
April 30, 2012 at 9:52 am
thanks for the info!
i was guessing that permissions were going to be the cause, but now i'm not sure.
when i goggled "reported a change in schema version between compile time ", it seemed to point to an issue where synonyms cause that error, but your example code doesn't have any synonyms.
there is a connect item that shows you can get that error, but it refers to SQL 2008:
i'm still poking around, but just to confirm, you are using 2005, right?
Lowell
April 30, 2012 at 9:57 am
correct, 2005(9.00.5266.00)
example code is an exact copy of prod, with names changed to protect the innocent 🙂
May 1, 2012 at 6:28 am
The error seems pretty clear. Something changed. Is the table being referenced another view or a user defined function?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 6:35 am
Grant Fritchey (5/1/2012)
The error seems pretty clear. Something changed. Is the table being referenced another view or a user defined function?
table being referenced is a standard table, that has had no structural changes since before 5/27/2010(initial entry into source control)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply