asked on
Weird behavior: unable to open or query table named scores, but renaming allows access
We have a SQL Server system with last quarters and this quarters databases, and within each is a table named Scores. We cannot open, query, or browse the Scores table in the current quarter's database. Renaming the table to "Scores1" lets us view the data, but renaming it back to "Scores" makes it fail again. When it fails, we alternatively get a blank window where we expect to see the table data, or we get an error message saying it doesn't exist or isn't available.
It gets more bizarre.. if we rename each of the two databases, effectively swapping the database names, then the problem table stays with the _name_, not the database!
We don't know what caused this, but in the last couple weeks, we've updated firmware and drivers, and SQL Server SP2 update. Event logs don't show anything interesting (to us). DBCC CHECKDB doesn't find anything wrong.
The system is an HP server connected to an HP SAN, running Windows Server 2003 Standard 32-bit, with SQL Server 2005 Standard SP2. The system is internal-use only, and accessed primarily by one programmer. There are no front-end apps, multiple-users.. just the one programmer.
Any ideas?
It gets more bizarre.. if we rename each of the two databases, effectively swapping the database names, then the problem table stays with the _name_, not the database!
We don't know what caused this, but in the last couple weeks, we've updated firmware and drivers, and SQL Server SP2 update. Event logs don't show anything interesting (to us). DBCC CHECKDB doesn't find anything wrong.
The system is an HP server connected to an HP SAN, running Windows Server 2003 Standard 32-bit, with SQL Server 2005 Standard SP2. The system is internal-use only, and accessed primarily by one programmer. There are no front-end apps, multiple-users.. just the one programmer.
Any ideas?
Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
chriso008
Which role does your user have when you connect to your database?
did you login as 'sa' sql server role member?
You wrote: " cannot open, query, or browse the Scores table in the current quarter's database. Renaming the table to "Scores1"
lets us view the data, but renaming it back to "Scores"
--
What did you mean - you can view data ? or not?
You wrote: " cannot open, query, or browse the Scores table in the current quarter's database. Renaming the table to "Scores1"
lets us view the data, but renaming it back to "Scores"
--
What did you mean - you can view data ? or not?
also;
did you install at least sp1 for sql server 2005?
what errors you can see in sql server log and Event viewer?
--------------------------
Do you have any views created on the table?
check
http://msdn.microsoft.com/en-us/library/ms187956.aspx
SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition
---
if your login has rights (sa; dbo): try to use
sp_rename
http://msdn.microsoft.com/en-us/library/ms188351.aspx
did you install at least sp1 for sql server 2005?
what errors you can see in sql server log and Event viewer?
--------------------------
Do you have any views created on the table?
check
http://msdn.microsoft.com/en-us/library/ms187956.aspx
SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition
---
if your login has rights (sa; dbo): try to use
sp_rename
http://msdn.microsoft.com/en-us/library/ms188351.aspx
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.