Microsoft KB Archive/238822

= INF: How to Rebuild the Repository in SQL Server 7.0 =

Article ID: 238822

Article Last Modified on 8/9/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q238822



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.
 * 1) Stop the SQLServerAgent service.
 * 2) Drop the existing msdb database.
 * 3) Use Query Analyzer to run the Run the Mssql7\Install\Instmsdb.sql script; this script creates the database and the necessary tables.
 * 4) Run the Repository Rebuild utility.
 * 5) Start the SQLServerAgent service.
 * 6) 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:

141530 INF: Rebuilding the MSDB Database in SQL Server 6.0 and 6.5

Keywords: kbinfo kbdownload KB238822

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.