SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid object name 'sys.spt_values'


Invalid object name 'sys.spt_values'

Author
Message
Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 2460
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213029 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34638 Visits: 9518
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."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222083 Visits: 46285
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, MVP, M.Sc (Comp Sci)
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


Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 2460
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



Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 2460
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 )



Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 2460
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



Indianrock
Indianrock
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 2460
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.



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