March 11, 2004 at 9:22 am
Hi all !
I recently got a strange problem.
After a corruption of two databases on our server (msdb and a user database), i was able to recover everything with a restore made with Legato.
Then, I noticed that creating a table from Enterprise Manager its type was System and not User.
After some digging in system tables, i've found that the new table was marked of type U but was also marked IsMSShipped (output of the objectproperty() t-sql function).
Seems that, for some unknown reason, the default behaviour of New Table command of the Enterprise Manager changed. Now it always creates "system" tables or, better, "MSShipped" tables.
I've extensively searched the web without finding anything about this problem.
Any clues ?
Thanks,
Mario
March 11, 2004 at 12:51 pm
The only way I know of to mark an object as system object is the undocumented s_proc sp_MS_marksystemobject. It sets some bits in sysobjects.
What happens when you create a table via Query Analyzer?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 12, 2004 at 1:15 am
I have created a table from Query Analyzer with this script:
CREATE TABLE [mario] (
[aaa] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bbb] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ccc] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The table is shown under the User Tables group in of the object browser treeview.
But, if I right click on it, I see that the option "script object to clipboard as DROP" is greyed.
The same table, from Enterprise Manager, is marked System and is not deleteable.
From Query Analyzer I can anyway use:
drop table [mario]
and get it erased....
This behaviour is strange, but in some way is also... coherent !!!
I think that something in sql went wrong at the same time i got those two database corrupted. Probably, some kind of... "default" changed from User to System. The problem is that I don't know where this default is stored...
March 15, 2004 at 8:24 am
UPDATE:
also newly created views are shown as System.
In sysobjects i can see different and negative values for their Status field.
Anybody knows where sql stores the default Status for a newly created table or view ?
tnx,
mario
December 24, 2006 at 1:51 am
am facing the same issue except any object created via QA, shows as User but can't create SP from Enterprise manager. Gives error, Owner name is not specified.
December 24, 2006 at 3:44 pm
Puneet,
Who is the owner of the object...Check UID of object in sysobjects table and make sure uid of the object exists in sysusers table...
MohammedU
Microsoft SQL Server MVP
December 24, 2006 at 3:53 pm
Mario,
Run the profiler to see what is happenning behind the scene when you create the object...
As Frank mentioned there is no other way than using undocumented proc to change the user object to system object...
MohammedU
Microsoft SQL Server MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply