Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid object name 'sys.spt_values' Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 2:09 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Sql 2012 Enterprise installation. user databases are all fine. Managing objects in management studio doesn't work. We know an application wrote its tables into the master database by mistake. I found the utables.sql script which will recreate the master database views, but it fails because it can't find sys.spt_values
[color=#FF0000]Invalid object name 'sys.spt_values' [/color]I have backups of master but was hoping this script would work.

create view spt_values as
select name collate database_default as name,
number,
type collate database_default as type,
low, high, status
from sys.spt_values
go



Post #1496579
Posted Thursday, September 19, 2013 8:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:57 PM
Points: 35,779, Visits: 32,451
Indianrock (9/19/2013)
Sql 2012 Enterprise installation. user databases are all fine. Managing objects in management studio doesn't work. We know an application wrote its tables into the master database by mistake. I found the utables.sql script which will recreate the master database views, but it fails because it can't find sys.spt_values
[color=#FF0000]Invalid object name 'sys.spt_values' [/color]I have backups of master but was hoping this script would work.

create view spt_values as
select name collate database_default as name,
number,
type collate database_default as type,
low, high, status
from sys.spt_values
go


I don't have 2k12 to confirm but try dbo.spt_values instead of sys.spt_values.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496685
Posted Thursday, September 19, 2013 9:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Jeff Moden (9/19/2013)

I don't have 2k12 to confirm but try dbo.spt_values instead of sys.spt_values.


I just tried and that works as long as you're in [master]. (If not, you just have to make it master.dbo.spt_values)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1496692
Posted Friday, September 20, 2013 3:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Honestly, if something's messed up master, I would recommend restoring it from the latest backup before whatever happened.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1496765
Posted Friday, September 20, 2013 6:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
dbo.spt_values doesn't exist, but even if it did can you create a view with the name matching the table it views? The only things not working are right clicking a table and selecting properties. right clicking anything else like a table seems to work. Also our Quest/Dell Spotlight tool won't monitor this instance for the same reason. I do have backups but on a cluster, restoring master would be a slightly big deal for me, not ever having restored master anywhere. I've removed all of the views/tables the accidental tool install put in master and since this is a production system would have to be done on Sunday probably. The u_tables script has this "-- Create a synonym spt_values in master pointing to spt_master in Resource DB, for backward compatibility" So how is this create view statement going to find spt_master in the resource DB?

create view spt_values as
select name collate database_default as name,
number,
type collate database_default as type,
low, high, status
from dbo.spt_values

Invalid object name 'dbo.spt_values'

-- sys.spt_values
go



Post #1496835
Posted Friday, September 20, 2013 7:17 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Restoring master from backup doesn't look all that daunting after a bit of reading. Now this is a two-node failover cluster so I'd want to make sure that the default instance in question comes back up on its normal node in single-user mode ( since I have the master backup set aside on that C drive for use with sqlcmd )


Post #1496878
Posted Friday, September 20, 2013 5:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Planning to restore master from backup, but that is probably a couple of weeks out. I've noticed that on our sql 2012 instances this view reveals 2,515 records. I can fairly easily either put those records into a table matching the spt_values structure ( in the master where its missing ) or put the data into "missingSpt" and create the spt_values view to look at that table. A bandaid until we can restore from master.
I have no idea what this could do



Post #1497083
Posted Friday, September 20, 2013 7:15 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Creating a temporary spt_values view of a temporary table containing the 2,515 rows worked. All of the functionality missing is now working. We still need to do the master restore. It sure would be interesting to know what "table" the spt_values view actually "views" I haven't been able to find that on other sql 2012 servers.


Post #1497091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse