Missing system stored procedures
-
I've recently noticed that when I create a new database using Enterprise Manager and select new database, there are no system stored procedures created. I looked back on previous databases and the system stored procedures are there as far back as April, but today they do not appear anywhere. I am running SQL Server 2000, SP3a. Any ideas of what's changed???
-
Is it possible that they are missing in the models db too?
-
Yep... I just checked it and sure enough they are missing out of the model. I'm guessing I can restore the model from a backup. Any idea how that happens in the first place?
-
No... but I'd check to make sure that the updates in the system catalogs are forbiden NOW... in case something really vitals gets whipped out.
-
There are NO system stored procs in user databases.
<SARCASM> Repeat NONE. That is, less than ONE </SARCASM>
That said, there may be in a replicated database.
I can't remember and I have no replicated DBs handy to check.
If you mean the "dt_%" rubbish, these are created when you right click a table, "Open Table", "Return all rows".
Try it in your model database (or "broken" user db) on one of the system tables
Now check for the dt_ rubbish.
If you still can't see them, then can you see sysusers, syspermissions etc?
If no, try this to make sure you are viewing it correctly.
In EM, right click the SQL Server, "Edit SQL Server registration properties...", look at the middle check box "Show system databases and system objects".
Remi, shame on you for not spotting this, although it's always worth checking updates to system objects is disabled.
Cheers, Shawn
-
Now you know I never worked with model to create server wide features for new db
.Thanx for the info about the time of creation of the dt%.
-
I double checked EM and I do have show system databases and system objects checked. I also looked at the master db and all these dt_% stored procedures are located here, so they exist along with all the sp_% one's. I take from the first comment that I really don't need them to be created in each database - although that seems to be what we've been doing in the past. My concern is now more that they used to be there and now they are not. I've found something regarding a newer version of MDAC that I'm pursuing to at least explain the difference.
Thank you for responding so quickly. I really appreciate the information I received and I do need to now that check on updates to system objects is disabled. Do you want to give me a direction to go as to where that is set???
-
If you really need your "missing" objects", as I said, and have you tried it:
If you mean the "dt_%" rubbish, these are created when you right click a table, "Open Table", "Return all rows".
Try it in your model database (or "broken" user db) on one of the system tables
Now check for the dt_ rubbish.
They are NOT SYSTEM PROCEDURES!
sp_xxx is a system stored proc and they only exists in master and msdb
They are not there because no-one has yet used EM to query the tables!
For the other:
--to check
EXEC sp_configure 'allow updates'
--to change
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
-
Well I had a clean Model db.
No dt procs to be seen.
I open a table with em.
Now I have dt procs in model.
They are listed as system procs.
I don't know what else to add to this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply