October 14, 2005 at 8:41 am
Hello !
One of my dear developpers unproperly used the stored procedure sp_MS_marksystemobject on User tables. Does anyone know how do turn the table type back to User ? The Database still works but, there are problems using the DTS... Which is very anoying...
Thank you !
October 14, 2005 at 9:34 am
You could create an exact copy of the table, insert all the data from the "system" table and then drop the system table.
A.J.
DBA with an attitude
October 14, 2005 at 9:39 am
Yes... but unfortunatly it's the production database....
October 14, 2005 at 9:44 am
Why did he make them system objects is really the first question to ask...
What is the problem with dts??
October 14, 2005 at 9:52 am
He did want to make those table system object... But he did...
Anyway, in the DTS we can't select "System" Table for replication that's why I try to turn those tables to User type...
October 14, 2005 at 9:52 am
well, you may be able to just update the status column in sysobjects. Now, what value to use when you update it, is a good question.
A.J.
DBA with an attitude
October 14, 2005 at 10:04 am
Maybe you could recreate the table under another name. Get its status there, then update directly the system tables (assuming you can't stop the server for a few sec to make this operation more safe).
TEST ON BACKUP FIRST.
October 14, 2005 at 10:06 am
ditto. I agree with RGR. Make sure you test this before you do it in your production environment.
A.J.
DBA with an attitude
October 14, 2005 at 10:08 am
Forgot that you'll have to change the xType to 'U' as well.
October 14, 2005 at 10:23 am
Might be an idea to create a user table on a test box and then run the sql to change the user table to system table, while this is happening have a trace running and presumably you should be able to see what is occuring and then reverse it.
October 14, 2005 at 11:49 am
You can get this info from just looking at the proc sp_MS_marksystemobject. It'll show the updates made, and then you can reverse those actions.
A.J.
DBA with an attitude
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply