INF: How to Rebuild the Repository in SQL Server 7.0
ID: Q238822
|
The information in this article applies to:
-
Microsoft SQL Server version 7.0
SUMMARY
This article describes the steps you need to perform to rebuild the SQL Server 7.0 repository, should it become necessary to do so.
MORE INFORMATION
To rebuild the repository, perform the following steps:
- Use the following script to drop all the tables in the msdb database starting with Rtbl%, Dbm%, Dtm%, Dts%, Gen%, Mds%, Ocl%, Olp%, Tfm%, Uml%, Umx% (where % represents the remaining characters in the table's name).
CAUTION: Before you execute the script, it is strongly recommended that you backup the MSDB database!
Use msdb
go
drop table DbmColumn
go
drop table DbmColumnSet
go
drop table DbmConnection
go
drop table DbmDataType
go
drop table DbmIndex
go
drop table DbmMiscellaneous
go
drop table DbmPackage
go
drop table DbmProvider
go
drop table DbmProviderDataType
go
drop table DbmProviderTypeMapping
go
drop table DbmReferentialRole
go
drop table DbmUmlOperation
go
drop table DtmNamespace
go
drop table DtmObjectType
go
drop table DtmObjectTypeMapping
go
drop table DtsBulkInsertTask
go
drop table DtsNode
go
drop table DtsOLEDBProperty
go
drop table DtsSendMailTask
go
drop table DtsStepExecution
go
drop table DtsTransferObjectsTask
go
drop table GenElement
go
drop table GenMiscellaneous
go
drop table MdsStore
go
drop table OclSequence
go
drop table OlpDimension
go
drop table OlpDimHierarchy
go
drop table OlpDimLevel
go
drop table OlpMappingLevelPair
go
drop table RTblClassDefs
go
drop table RTblDatabaseVersion
go
drop table RTblIfaceDefs
go
drop table RTblIfaceMem
go
drop table RTblNamedObj
go
drop table RTblPropDefs
go
drop table RTblProps
go
drop table RTblRelColDefs
go
drop table RTblRelshipProps
go
drop table RTblRelships
go
drop table RTblRoot
go
drop table RTblSites
go
drop table RTblSumInfo
go
drop table RTblTypeLibs
go
drop table RTblVersionAdminInfo
go
drop table RTblVersions
go
drop table RTblWorkspaceItems
go
drop table TfmCodeDecodeValue
go
drop table TfmDependency
go
drop table TfmElement
go
drop table TfmPackage
go
drop table TfmStepExecution
go
drop table TfmTransformation
go
drop table TfmTransformationTask
go
drop table UmlAssociationRole
go
drop table UmlAttribute
go
drop table UmlElement
go
drop table UmlGeneralizableElement
go
drop table UmlMember
go
drop table UmlNote
go
drop table UmlOperation
go
drop table UmlParameter
go
drop table UmlPoint
go
drop table UmlProjection
go
drop table UmlReference
go
drop table UmlSignalReference
go
drop table UmlStereotype
go
drop table UmlTaggedValue
go
drop table UmlType
go
drop table UmlValue
go
drop table UmxMiscellaneous
go
- Use the script below to drop all stored procedures starting with r_%. This step is not always required and should be executed if you are experiencing problems with the repository's stored procedures. These stored procedures are part of the repository.
Use msdb
go
drop proc r_iUmlType
go
drop proc r_iUmlValue
go
drop proc r_iTfmStepExecution
go
drop proc r_iUmlAssociationRole
go
drop proc r_iTfmTransformationTask
go
drop proc r_iDbmConnection
go
drop proc r_iUmlTaggedValue
go
drop proc r_iDtsSendMailTask
go
drop proc r_iTfmElement
go
drop proc r_iTfmDependency
go
drop proc r_iDbmColumnSet
go
drop proc r_iRTblSumInfo
go
drop proc r_iOlpDimension
go
drop proc r_iGenMiscellaneous
go
drop proc r_iOlpMappingLevelPair
go
drop proc r_iOlpDimLevel
go
drop proc r_iGenElement
go
drop proc r_iOlpDimHierarchy
go
drop proc r_iDbmColumn
go
drop proc r_iUmlAttribute
go
drop proc r_iUmlSignalReference
go
drop proc r_iUmlStereotype
go
drop proc r_iUmlNote
go
drop proc r_iTfmTransformation
go
drop proc r_iUmlPoint
go
drop proc r_iDbmProvider
go
drop proc r_iDtmNamespace
go
drop proc r_iRTblVersions
go
drop proc r_iDbmReferentialRole
go
drop proc r_iRTblRelships
go
drop proc r_iRTblSites
go
drop proc r_iRTblProps
go
drop proc r_iRTblRelshipProps
go
drop proc r_iRTblPropDefs
go
drop proc r_iRTblRelColDefs
go
drop proc r_iRTblIfaceDefs
go
drop proc r_iRTblClassDefs
go
drop proc r_iDbmMiscellaneous
go
drop proc r_iRTblTypeLibs
go
drop proc r_iMdsStore
go
drop proc r_iDbmPackage
go
drop proc r_iDtmObjectTypeMapping
go
drop proc r_iDtmObjectType
go
drop proc r_iDbmDataType
go
drop proc r_iRTblIfaceMem
go
drop proc r_iRTblWorkspaceItems
go
drop proc r_iRTblRoot
go
drop proc r_iDbmProviderDataType
go
drop proc r_iRTblNamedObj
go
drop proc r_iDbmProviderTypeMapping
go
drop proc r_iDbmIndex
go
drop proc r_iUmlGeneralizableElement
go
drop proc r_iUmlOperation
go
drop proc r_iUmlElement
go
drop proc r_iOclSequence
go
drop proc r_iTfmCodeDecodeValue
go
drop proc r_iUmlReference
go
drop proc r_iRTblVersionAdminInfo
go
drop proc r_iDtsNode
go
drop proc r_iUmlMember
go
drop proc r_iDtsOLEDBProperty
go
drop proc r_iDbmUmlOperation
go
drop proc r_iDtsStepExecution
go
drop proc r_iUmlParameter
go
drop proc r_iDtsBulkInsertTask
go
drop proc r_iTfmPackage
go
drop proc r_iUmlProjection
go
drop proc r_iDtsTransferObjectsTask
go
drop proc r_iUmxMiscellaneous
go
- Performing the preceding two steps should remove the existing repository. The next step is to rebuild the repository, using the utility extracted from RepRbld.exe, located at the SQL Server Downloads page at:
RepRbld.exe
Before running this utility, please read the Readme.txt file and follow the instructions on how to use it.
NOTE: It is important to remove the repository (that is, do steps 1-2 above) if you completely want to rebuild it because this utility does not re-create tables if they already exist.
In some cases, you may not be concerned about the existing msdb, and it may be easier to completely rebuild msdb. In those situations, you can drop and re-create msdb and then run the Repository Rebuild utility. To rebuild msdb, perform the steps below.
WARNING: When you rebuild msdb, any existing tasks, alerts, operators, replication information, Data Transformation Services (DTS) packages saved locally, and DTS packages saved in the repository will be lost.
- Stop the SQLServerAgent service.
- Drop the existing msdb database.
- Use Query Analyzer to run the Run the Mssql7\Install\Instmsdb.sql script; this script creates the database and the necessary tables.
- Run the Repository Rebuild utility.
- Start the SQLServerAgent service.
- Test the rebuild by attempting to add tasks, alerts, and
operators, save DTS packages, and so on.
For information about how to rebuild msdb, on SQL Server 6.0 or 6.5, please click the article number below to view the article in the Microsoft Knowledge Base:
Q141530 INF: Rebuilding the MSDB Database in SQL Server 6.0 and 6.5
Additional query words:
Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo
|