Microsoft SQL Server 7.0 Administrative Command Line Utilities (Part 1)
September 5, 2002
In my latest article, I'm going to briefly touch on thirty useful administrative command line utilities for Microsoft SQL Server 7.0. The tools described below primarily serve for diagnostics, configuring and migration, rather than for database server administration. Some of these tools (for example Bcp, Distrib and Osql) have complex syntax (set of command line parameters), meaning you'll likely need permanent access to Microsoft SQL Server 7.0 electronic documentation - Books Online, which is available with your database server compact disc.
Bcp - A utility intended for bulk copying of SQL Server data (can be any table, view or Transact-SQL result set) and/or data files (in a user-specified format) or any other database management systems. Bcp is frequently used to transfer large volumes of data among Microsoft SQL Server databases and other RDBMS databases.
Cnfgsrv - An undocumented utility -- try to run it with the /? option to receive a set of acceptable command line parameters. Microsoft SQL Server probably uses cnfgsrv.exe when the setup attempts to configure the SQL Server computer.
Console - An operating system command prompt utility that displays informational messages during backing up SQL Server databases or when restoring databases from tape dump devices.
Dcomscm - A COM-component used for SQL Server program management and its services state checking.
Distrib - A Replication Distribution Agent command prompt utility that configures and begins a replication Distribution Agent, which moves transactions and snapshot jobs held in the distribution database tables to Subscribers.
Dtsrun - A utility for retrieving, executing, deleting and overwriting packages, previously created using Microsoft Data Transformation Services. The DTS package can be stored in the Msdb database, a COM-structured storage file or Microsoft Repository.
Dtswiz - A utility for running Microsoft Data Transformation Services import and export wizards from a command prompt and creating DTS packages that import, export or transform data among different ODBC-data sources.
Isql - A utility for interactive Transact-SQL statements, system procedures and SQL Server scripts execution. Isql uses DB-libraries for database server connections started directly from the operating system command prompt and uses standard output devices (monitor, by default) for displaying results.
Kill6x - A utility for removing «old», 6.x versions of Microsoft database servers.
Langinst - A utility for installing additional languages support through the SQL Setup application.
Logread - A utility used for configuring and starting a replication Log Reader Agent. All transactions (marked for replication) move from the transaction log on the Publisher to the distribution database.
Makepipe - A diagnostic utility used for network Named Pipe services integrity testing (checks if working). The Makepipe utility should be launched from the database server console, and it expects connection from Readpipe, which should be started at the problem SQL-client console. After Makepipe is started, the database server waits for a client to connect. In other words, Makepipe is used in conjunction with Readpipe utility (see below).
Odbccmpt - A migration assistance utility that enables or disables the Microsoft SQL Server 6.5 ODBC compatibility option for an ODBC application executable file. Odbccmpt should be used ONLY (!!!) for applications having problems when moved to SQL Server 7.0.
Odbcping - A diagnostic utility every DBA should use for testing ODBC data sources integrity (in case of ODBC-related connection problems) and for establishing successful connections between the SQL client and database server.
Osql - A utility for interactive Transact-SQL statements, system procedures and SQL Server scripts execution. Osql uses ODBC libraries for database server connection and is started directly from the operating system command prompt. Osql uses standard output devices (monitor, by default) for displaying results.
Readpipe - A diagnostic utility used for network Named Pipe services integrity testing (checks if working). Readpipe is launched on a SQL client console that is unable to connect to SQL Server. Readpipe is used in conjunction with Makepipe utility (see above).
Rebuildm - A utility used for (1) fixing a corrupt master database, and (2) changing the character set, sort order or Unicode collation for a Microsoft SQL Server installation. Be sure you have full dumps for all user databases or all de-attached data and log files for all user databases. After rebuilding the master database you need to re-create the database objects and reload the data, or attach the data and log files for all databases you need using the sp_attachdb system stored procedure.
Regrebld - A utility database server administrators use to backup and restore Microsoft SQL Server 7.0 entries in the system registry if they become corrupted.
Replmerg - A Replication Merge Agent utility that configures and starts a replication Merge Agent and then applies initial snapshots held in the database tables of the Publisher to the Subscribers. Replmerg also merges data changes made by the Publisher or Subscribers after the initial snapshot is created.