We have SQL2012 SP1 installed on several servers and have run into an issue on one instance trying to view the database properties in SSMS. (This happens with both SSMS 11.0.5058.0 and 11.0.3128.0.) I have crawled all over Google results looking for something that makes sense, but I'm baffled at the moment.
The issue is rooted with a view in the master database called dbo.spt_values. This table is referenced in the error thrown by SSMS when trying to view the properties.
The recommended solution here is to run the u_tables.sql script with will recreate the necessary objects in the master database. Opening and running this script produces an error stating:
Msg 208, Level 16, State 1, Procedure spt_values, Line 7
Invalid object name 'sys.spt_values'.
Most of the posts I have seen related to this error recommend completely rebuilding the master database at this point, then reattaching or restoring the user databases. Before I went down this road, I wanted to see what the servers not experiencing this problem look like… To further my befuddlement, I am not able to see sys.spt_values ON ANY INSTANCE OR ANY DATABASE. However, the master.dbo.spt_values view does return rows when queried. When I try to take the select statement out of the view and run it, I get the invalid object message again.
I have connected as my windowd users, which is sysadmin, as well as the SA login. I have done this on each of the servers as well as a local instance of SQL 2012 SP2. I have used SSMS installed with the original SP1 installation as well as an instance that had been upgraded to SP2.
I have also looked at synonyms, other views, functions, stored procedures, or any other objects I could parse or query and have no idea where I can find the sys.spt_values table. Any help to get the properties box working as well as improve my understanding of the purpose of this table and where it lives would be monumentally helpful!
Resources I've already looked at:
- http://www.experts-exchange.com/Database/MS-SQL-Server/Q_21958187.html
- http://answers.flyppdevportal.com/categories/azure/sqlazure.aspx?ID=92d22012-b829-4823-ab3a-7195fcf2dcb4
- http://social.msdn.microsoft.com/Forums/en-US/3343a3a6-bd4a-48d6-b87f-2480ef8b597c/master-db?forum=sqldatabaseengine
- http://web.archive.org/web/20150411043738/http://sqlserver2000.databases.aspfaq.com/why-do-i-get-errors-about-master-spt-values.html
- http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx
(I also followed links within the links… Good thing I'm bald already)
Best Answer
Answer compiled from comments by the question author
Following a suggestion by Ben Thul to try connecting using the Dedicated Administrator Connection (DAC), that was it: I was able to connect over the DAC wtih SQLCMD and run
From there I ran the u_Tables.sql script and it looks like we're good to go.
I'm not familiar everything that is different when using the DAC, but that connection allowed me to see the sys.spt_values object. I had not been otherwise able to query against it. Since the object was now visible over the DAC, I could execute the u_tables.sql rebuild script successfully.
It is still unclear what caused the original issue. I was contacted by a client regarding the error in our Test environment. The issue did not impact any other functionality besides the properties dialog box in SSMS.
Several sources said to run the u_tables.sql script to fix the issues. When I inspected the script, I tried to run just the sql within the view and kept getting the error about an invalid object.
This was true regardless of how I connected through SSMS. It also meant the u_tables.sql would not execute successfully. The DAC connection allowed me to see the necessary objects.