SQL Server 2014: Re-Create System view master.dbo.spt_values -


on test sql server 2014 installation, "cleaning" master database.

with following command, checking user objects there are:

select 'drop ' +     case          when [sys].[all_objects].type in ('af','fn','fs','ft','if','tf') 'function '     when [sys].[all_objects].type in ('d','c','f','pk','uq') 'constraint '     when [sys].[all_objects].type in ('it','s','u') 'table '     when [sys].[all_objects].type in ('p','pc','rf','x') 'procedure '     when [sys].[all_objects].type in ('ta','tr') 'trigger '     when [sys].[all_objects].type = 'r' 'rule '     when [sys].[all_objects].type = 'sn' 'synonym '     when [sys].[all_objects].type = 'tt' 'type '     when [sys].[all_objects].type = 'v' 'view ' end  +     schema_name(sys.[all_objects].[schema_id]) + '.' + object_name(object_id) + '; ' [command],      object_name(object_id) [objectname],      [sys].[all_objects].[type_desc] [typedesc],      [sys].[all_objects].[type] [type],      schema_name(sys.[all_objects].[schema_id]) [schema]      sys.[all_objects] (nolock)  schema_name(sys.[all_objects].[schema_id]) '%dbo%'

one of results view spt_values. 

command                 | objectname | typedesc | type | schema ------------------------|------------|----_-----|------|------- drop view dbo.spt_values; spt_values   view       v      dbo

as not 1 of views knew, deleted (along other objects).

later day, wanted check properties of database in ssms 2016 , got following error:

invalid object name 'master.dbo.spt_values'

after searching, found recreate missing view script u_tables.sql (which in sql server installation folder on server). information here: https://ashishgilhotra.wordpress.com/tag/u_tables-sql/

the code in script create view following:

create view spt_values select name collate database_default name,     number,     type collate database_default type,     low, high, status sys.spt_values go  exec sp_ms_marksystemobject 'spt_values' go  grant select on spt_values public go

already when looking @ code, doubted work, there no sys.spt_values table anywhere found.

as expected error

msg 208, level 16, state 1, procedure spt_values, line 6 invalid object name 'sys.spt_values'.

on other server sql server 2008 on it, there table master.dbo.spt_values (but no view)!

after more searching, found create table same name.. link here https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/

now create table values sql server 2014 installation, , seems working again.

but, not correct!

when check new created object on test server command

select [name] , [type], [type_desc] sys.objects name 'spt_v%'

it shows user_table object. on other server, shows view...

so, question is: how can create view spt_values gets data table spt_values?

ok, after fiddling arround, found solution..

the table sys.spt_values in ressources database (mssqlsystemresource). database accessible when sql service started in single user mode..

to re-create view had following steps:

  1. stop sql services
    stop sql services


2. start sql service in single user mode
open dos command prompt , start sqlservice switch -m

sqlservr.exe -ssqlt01 –m


  1. connect ssms instance

just connect query window, not object explorer window. service accepts 1 single connection! if there problem, can see in dos window service running.

  1. delete wrong table spt_values

as created table spt_values on master database, have delete first

use master go drop table dbo.spt_values


5. create view
can create view dbo.spt_values, points table sys.spt_values

use master go create view spt_values select name collate database_default name,     number,     type collate database_default type,     low, high, status sys.spt_values go  exec sp_ms_marksystemobject 'spt_values' go  grant select on spt_values public go


6. check dbo.spt_values object

use master select schema_name(schema_id), object_id('spt_values'), *  sys.objects name 'spt_v%'

it should show view now

  1. query view dbo.spt_values , table sys.spt_values

just fun of it... can query table sys.spt_values, in ressources database

use mssqlsystemresource select * sys.spt_values


, can query view dbo.spt_values, in master database

use master select * dbo.spt_values


8. restart services

you can quit dos window sql service running , start sql services. or restart whole server

hope post others in future


Comments

Post a Comment

Popular posts from this blog

inversion of control - Autofac named registration constructor injection -

does autofac support specifying registration name in components' constructors? example: ninject's namedattribute . you need use autofac.extras.attributed package on top achieve this so let's have 1 interface , 2 classes: public interface ihello { string sayhello(); } public class englishhello : ihello { public string sayhello() { return "hello"; } } public class frenchhello : ihello { public string sayhello() { return "bonjour"; } } then have consumer class, in want select instance injected: public class helloconsumer { private readonly ihello helloservice; public helloconsumer([withkey("en")] ihello helloservice) { if (helloservice == null) { throw new argumentnullexception("helloservice"); } this.helloservice = helloservice; } public string sayhello() { return this.helloservice.sayhello()...
Read more

verilog - Systemverilog dynamic casting issues -

i've code snippet following in testbench function void write_to_port( my_data_type_base data ); my_data_type_extended data_ext; if(!$cast(data_ext, data)); `uvm_error(get_type_name(), "failed cast"); `uvm_info(get_name(), $psprintf("data_ext :\n%s", data_ext.sprint()), uvm_medium) // write data_ext out port.... endfunction when run it, i'm getting uvm_error "failed cast." i'm not quire sure why $cast not returning 1. can see, i'm printing out extended class data item after casting uvm_info. can see it's being cast properly. if don't use $cast if condition, don't runtime error. isn't coding practice use if dynamic cast check if $cast returning 1 ? what might reason behind cast not returning 1 in above case? i think semicolon on line 'if' not belong? i think consumes if statement, , uvm_error executes regardless of how if evaluates: if(!$cast(data_ext, data)); <- no semico...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more