Repairing database system tables

[Massinova]

Recently, I modified a table in my database (removing a field and renaming another). Apparently, the changes didn't fully register with one of the database system files.

Is there a way of repairing or correcting the system database tables in a graceful manner without restoring the original backups?

Also, is there a best practice procedure to changing a database table and prevent system table corruption?

 

[mrdenny]

What do you mean that the changes didn't register with one of the database system files? How are you seeing this? What are you seeing in the database?

Updating the system tables while doable isn't recommended or supported.

What problems are you seeing with the database? How did you make the table changes?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[Massinova]

Ok, here's a brief history:

-The database was originally created in MS Access and upgraded to SQL server.

- Eventually, changes were made to certain fields in one of the database tables because they were unused, obsolete, and/or confusing to the managers/users. These changes were made through an adp project. Also, these modified fields neither were primary keys nor were they indexed ...they served no purpose and contained no data.

- Basically, I could review and see the modified table in SQL Enterprise manager, but if I attempted to open the modified table through an access adp project, I'd get an error saying that <the old modified field name> does not exist. But the table would open up in design view showing the new/modified field name?

- I eventually found the problem in the sysproperties table where it apparently didn't register the name change of the field causing problems.

- I modified the sysproperties file to reflect the correct name change, but noticed that the fields that I deleted were still being referenced and the field sequencing seemed to reflect pre-modified conditions. The minor change seemed to fix the problem with opening the database table in adp project, but I noticed that there was an unusual hesitation when opening the modified database table. I assuming that the corrupted sysproperties table, which has fields that are out of sync and reference to non existing fields, is causing this hesitation.

Anyway I suppose I could manually modify the sysproperties table, but there are things in the table I'm unsure about and I'm reluctant to modify it anymore.

 

[mrdenny]

The sysproperties table holds the extended properties that have been assigned to objects.

This table isn't critical to the operation of the SQL Server. It's used most by Enterprise Manager and aparently access uses it to tie the local tables to the SQL tables.

You should never modify the system tables by hand.

Changes to objects in the SQL Server should not be done through Access. They should be done through SQL Enterprise Manager or through T/SQL scripts.

To fix the origional problem you should have needed to unlink the table and relink it within access.

The records within the sysproperties table are linked to the table by the id field and to the column by the smallid field. When Access moved the columns around it didn't change the extended properties.

Go into Enterprise Manager and bring up the design view of the table. You'll see the description field at the bottom. You can move the descriptions around so they are in the correct place and save the table design.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[Massinova]

I just wanted to do a follow up on this thread.

Basically I found that if you use the upsize wizard to convert an access mdb to and apd, the sysproperties and some other tables will be populated.

If you export the individual tables to its designated SQL database, the sysproperties (and some of the other tables) will not be populated.

I've come to the conclusion that it's far safer to export your individual access tables than upsize the project. A populated sysproperties table does not like to accommodate changes to the table structure and seems to cause general havoc for access when looking at the data of an altered table structure.