- The Microsoft SQL Server Program Group
- The Microsoft SQL Server–Switch Program Group
- Other SQL Server Tools/Utilities
- Summary
- Q&A
This chapter is from the book
This chapter is from the book
This chapter is from the book
Other SQL Server Tools/Utilities
Several utilities that don't have shortcuts on your Start menu are available. These utilities, however, can be very useful. They are grouped into connectivity tools, server diagnostics, and maintenance utilities.
Connectivity Tools
Connectivity tools are the command-line utilities that provide a query interface or are network testing utilities. The first two tools you examine, OSQL and ISQL, allow you to connect to SQL Server and run Transact-SQL commands. They are the command-line equivalents of the SQL Server Query Analyzer. The second two sets of utilities, makepipe/readpipe and ODBCPing, let you test the network connectivity to your SQL Server.
OSQL
The OSQL.exe utility provides an ODBC-based query interface to SQL Server. This utility uses ODBC to connect to SQL Server. You can use this utility to run batch queries to support production tasks. You can also easily script Windows command files to run OSQL and run Transact-SQL commands to add new data, change data, or remove data from your databases. You can also create scripts (as you saw for the SQL Server Query Analyzer) and then run them without having to use a graphical interface. OSQL can be called with two different sets of options:
osql [-L]| [-?]
In this simple syntax,
-L shows a list of all SQL Servers found on the network.
-? is the standard request for this syntax list.
osql {{-U login_id [-P password]} | [-E]} [-S server_name] [-e] [-p] [-n]
[-I] [-b] [-O] [-u] [-R] [-d db_name] [-q "query"] [-Q "query"]
[-c cmd_end][-h headers][-w column_width] [-s col_separator] [-t time_out]
[-m error_level] [-r {0 | 1}] [-H wksta_name] [-i input_file]
[-o output_file] [-a packet_size] [-l time_out] [-D DSN]
In this more complete syntax,
-U is your SQL Server login ID.
-P is the SQL Server login password. If you don't enter it, OSQL prompts you to enter it when the program runs. If you've set an environment variable named OSQLPASSWORD, this password is attempted before prompting you for a password.
-E requests a Windows Authentication Mode connection, so you don't need to specify the -U or -P parameters.
-S tells OSQL which server to connect to, in the form SERVER\Instance. If a server isn't specified, OSQL connects to the local default instance.
-e echoes each statement you run in the output from that statement.
-p prints out performance information for your queries.
-n removes the numbers and the > prompt that OSQL normally includes in each row when you enter a set of commands.
-I specifies that QUOTED_IDENTIFIER should be turned on for your connection. This will determine whether items in quotes are taken as string constants ("hello there") or as column or table names.
-b tells OSQL to set the DOS error level when an error occurs. OSQL returns 1 when an error message with a severity level higher than 10 occurs.
-O tells OSQL to emulate ISQL for backward compatibility.
-u tells OSQL that the results of your query in your output file should be in Unicode.
-R allows client-side conversion when converting money and date time values from SQL Server.
-d specifies which database to switch to when you connect.
-q tells OSQL to run the query you enclose in quotation marks when it starts. OSQL continues to run after running the query. If you must specify quotation marks within the query, use double quotes around the query and single quotes in your query.
-Q tells OSQL to run the query you enclose in quotation marks when it starts and then quit osql.exe.
-c sets the batch separator indicator. In SQL Server scripts, the word GO tells SQL Server to submit your queries to SQL Server as a group (known as a batch). However, you can override this and use your own indicator to OSQL to submit your queries to SQL Server. You shouldn't override this option.
-h indicates to OSQL how many rows to print between your column headings and your query results. If you specify -h-1, no headings are produced for your queries.
-w allows you to override the width of your output from the default of 80 characters.
-s allows you to override the default column separator of a blank space.
-t tells OSQL how long to wait before it considers your connection to the server to be a failure.
-m changes error message reporting. The syntax is -m n, where n is the severity level of errors. Day 20 explains error severity.
-r indicates that error messages should go to the stderr device. If this is set to 0, only severity 17 or higher messages are sent to the stderr device. A setting of 1 indicates that all messages go to the stderr device.
-H is your computer name if you want to send it to SQL Server.
-i is the pathname and filename of the Transact-SQL script you want run.
-o is the file in which you want your results from your script to be stored. The output file is in Unicode if your input file was in Unicode.
-a indicates the packet size to use on the network.
-l (that's a lowercase letter L, not a number 1) tells OSQL the login timeout (how long before it's assumed that your server isn't running).
-D tells OSQL the name of a SQL Server Data Source Name defined in the Data Sources (ODBC) program in your administrative tools folder (or those that were created programmatically). This option works only for SQL Server data sources.
To run the commands you ran earlier for the SQL Server Query Analyzer, you would see the following in your command prompt:
TIP
For this (and most) command-line tools, you can specify parameters with either a - or a /. So, for the servername parameter, you could run either OSQL /S or OSQL -S. They mean the same thing.
C:\>osql /E 1> use pubs 2> select * from authors 3> go
Then you would see your query results displayed, ending in the following:
893-72-1158 McBadden Heather
707 448-4982 301 Putnam
Vacaville CA 95688 0
899-46-2035 Ringer Anne
801 826-0752 67 Seventh Av.
Salt Lake City UT 84152 1
998-72-3567 Ringer Albert
801 826-0752 67 Seventh Av.
Salt Lake City UT 84152 1
...
(23 rows affected)
1>
The GO keyword tells OSQL to begin running the command(s) you've specified. The 1> at the end of the output indicates that OSQL is ready to accept a new Transact-SQL command. Two other commands worth noting are Reset and Exit:
Reset stops any command and returns you to the 1> prompt.
Exit leaves OSQL.
A batch file might look like this:
osql -E -I"D:\program files\Microsoft SQL Server\mssql\runquery.sql" _ -oc:\results.txt
The input file would contain your queries, and the results would show up in the results.txt file. This command would make a Windows authenticated connection to SQL Server. You could then run this batch file any time and even schedule this batch file to run at some scheduled time in the future.
ISQL
ISQL.exe is the command-line query tool from previous releases of SQL Server. It uses the DB-Library network library to connect to SQL Server. Because it's based on DB-Library, it doesn't understand or can't work with SQL Server's new features, including Unicode. For this reason, you should discontinue using this utility if you have batch jobs already set up, and you should definitely not start using this tool now. It's only mentioned here for the sake of completeness as you may run into it from earlier installations of the product.
isql -U login_id [-e] [-E] [-p] [-n] [-d db_name] [-q "query"]
[-Q "query"] [-c cmd_end] [-h headers] [-w column_width]
[-s col_separator] [-t time_out] [-m error_level] [-L] [-?]
[-r {0 | 1}] [-H wksta_name] [-P password] [-S server_name]
[-i input_file] [-o output_file] [-a packet_size]
[-b] [-O] [-l time_out] [-x max_text_size]
Many of these parameters are similar to OSQL.exe, but you really shouldn't use this utility in SQL Server 2000 except to support jobs that ran in previous releases.
Makepipe/Readpipe
You use the makepipe and readpipe utilities to verify the integrity of the Named Pipes file system. Because SQL Server 2000 doesn't use Named Pipes by default anymore, these utilities aren't installed by default. If you are having problems with Named Pipes, you can copy them from the x86\binn directory from your installation CD into your \binn directory for your SQL Server. You can find further directions on using these utilities in the SQL Server Books Online.
ODBCPing
ODBCPing enables you to verify that ODBC is working successfully from a client to a connection to SQL Server.
odbcping [-?] | [{-Sserver | -Ddatasource} [-Ulogin] [-Ppassword]]
In this syntax,
-S is the server to which you want to connect.
-D is the name of an ODBC data source.
-U is the login ID you're using to connect to SQL Server.
-P is the password for the login ID you've chosen.
SQL Server 2000 doesn't install this program by default. However, it's in the x86\binn directory on your SQL Server 2000 CD. Copy the utility into your \binn directory (d:\Program Files\Microsoft SQL Server\mssql\binn for your default instance following our installation instructions), and then try the following to test that connection.
Go to a command prompt and enter the following to connect to your copy of SQL Server; specify your server name instead of mine (RHOME\Trade):
odbcping -Srhome\trade -Usa -P
The server should respond with output similar to this:
F:\>odbcping -Srhome\trade -Usa -Ppassword
CONNECTED TO SQL SERVER
ODBC SQL Server Driver Version: 2000.80.0100
SQL Server Version: Microsoft SQL Server 2000—8.00.100 (Intel X86)
Apr 18 2000 01:19:00
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: )
This output means that ODBC is working fine. You can also connect to an ODBC DSN (Data Source Name), which is a preset configuration to a server you configure with the Data Sources (ODBC) application in your Administrative Tools folder.
NOTE
The ODBCPing tool doesn't support integrated security connections, so you can't use it against a default installation of SQL Server that's in integrated security–only mode (such as your default instance).
Server Diagnostics/Maintenance Utilities
The Server Diagnostic/Maintenance utilities are a set of utilities and tools you use at various times after you install SQL Server 2000.
SQLServr.exe
SQLServr.exe is the actual program that runs SQL Server (for a default instance, the MSSQLServer service). However, if you want to, you can run SQL Server from a command prompt. You would usually run it this way if you had to start SQL Server in what's known as single-user mode. You'll examine this procedure in more detail on Day 8, "Restoring Your Databases," because that's when you typically must run SQL Server in single-user mode.
sqlservr [-sinstancename] [-c] [-f] [-dmaster_path] [-lmaster_log_path] [-m] [-n][-eerror_log_path] [-pprecision_level] [-Ttrace#] [-v] [-x] [-g number] [-O] [-y number]
In this syntax,
-s specifies the instance name that you are starting. Leave off this parameter when you're manually starting a default instance of SQL Server. Otherwise, you must specify this option, even when you are calling SQLServr.exe from the \binn directory of a named instance.
-c indicates that SQL Server should run as a program, and not as a Windows 2000 service. Using this parameter makes SQL Server start more quickly in a command window.
-f indicates that SQL Server should start in a "minimal" configuration. You would specify this option when you manually set a configuration setting that prevents SQL Server from starting normally. It's an emergency mode meant to allow you to fix any mistakes you make.
-d indicates the pathname and filename of your master database file. If you don't specify this option, the default you set during setup is found in your Registry and used. The default location is D:\Program Files\Microsoft SQL Server\mssql\data\master.mdf, or whatever drive on which you chose to install SQL Server.
-l indicates the pathname and filename of your master database transaction log file. If you don't specify this option, the default you set during setup is found in your Registry and used. The default location is D:\Program Files\Microsoft SQL Server\mssql\data \master.ldf, or whatever drive on which you chose to install SQL Server.
-m indicates that SQL Server will start in single-user mode, and only one user is allowed to connect to SQL Server at any time with a single connection. You can set this method during recovery situations after losing critical data files (such as recovering your master database from a backup).
-n turns off error logging to the Windows NT/2000 Event Log (not recommended).
-e is the pathname and filename of the SQL Server error log. It defaults to D:\Program Files\Microsoft SQL Server\mssql\log\errorlog, or whatever drive on which you chose to install SQL Server.
-p is the maximum precision to allow for the decimal and numeric data types. By default, SQL Server allows these data types to hold up to 38 digits of precision. However, you can change this default by specifying a number here, from 1 to 38. Specify this option if you don't want to allow such large numbers in SQL Server or for backward compatibility with a previous release (such as SQL Server 7.0) that didn't support that much precision by default.
-T specifies a trace flag to use in SQL Server. A trace flag is a numeric switch that tells SQL Server to enable special (nonstandard) behavior. You would typically use these flags only when directed to do so by SQL Server Product Support. To specify more than one, use multiple -T options.
-v displays the version number of sqlservr.exe.
-x turns off SQL Server performance statistics (not recommended).
-g specifies the amount of memory you want to reserve for applications (such as extended stored procedures) running in process with SQL Server. You shouldn't modify this advanced configuration option unless instructed to do so.
-O turns off DCOM and distributed queries. Set this option if you know you never want to run distributed queries.
-y takes an error number as a parameter. When specified, this option writes the stack dump to your SQL Server error log when this error number is encountered. Again, you shouldn't use this option unless instructed to do so by a SQL Server support engineer.
For example, stop SQL Server with the SQL Service Control Manager or SQL Server Enterprise Manager, and then open a command prompt. Switch to your default instance's \binn directory and type the following:
D:\Program Files\Microsoft SQL Server\MSSQL\Binn>sqlservr.exe -c
SQL Server runs in that command window and looks like Figure 3.34 when it's ready for you to begin logging in with a query tool.
){kind=link}
Figure 3.34 SQL Server when running in a command window.
To stop sqlservr.exe, press Ctrl+C with the command window selected, and you are prompted with
Do you wish to Shutdown SQL Server (Y/N)?
Type Y, and SQL Server stops. You can then restart SQL Server as a service.
The Rebuild Master Utility
The Rebuild Master utility rebuilds your master database, as well as the msdb, model, tempdb, pubs, and Northwind databases. You would run this utility for the following reasons:
You lose a hard drive and don't have any backups.
You want to change the default collation settings.
Your master database has become corrupt.
When you run the Rebuild Master utility, new copies of each database are made from your SQL Server 2000 CD, and then the collation choice you've made is applied. Any databases (in addition to those just mentioned) defined in your previously running SQL Server are no longer defined, and you need to re-create all your databases, or "reattach" the databases to your SQL Server.
You typically rebuild the master in a disaster recovery scenario, which is where you will examine the utility further on Day 8.
The Registry Rebuild Option
You can use the Registry Rebuild option when your Windows Registry becomes corrupted for some reason or when you are instructed to do so by Microsoft SQL Server Product Support. Registry Rebuild simply re-creates all the Registry keys that were built during your previous setup.
To run Registry Rebuild, rerun setup, select to maintain your existing installation, specify the instance name if you're working against a named instance, and then select Advanced Options. Select Registry Rebuild from the menu, and then follow through setup. Select exactly the same options you chose during your initial setup to return the Registry to a usable state.
SQLMaint.exe
The SQLMaint.exe command supports routine system maintenance, including backup, consistency checking, and index maintenance. A wizard in SQL Server Enterprise Manager, called the Database Maintenance Plan Wizard, helps you configure maintenance plans and backups for your databases without having to know a lot about what's going on behind the scenes. The wizard is actually configuring automated jobs to call this utility. You have many options here, most of which won't make much sense now. However, by Day 18, "Scheduling Jobs with SQL Server Agent," when you learn to configure the SQL Server Agent, including setting up scheduled jobs, you should review this syntax and look at how to use this program to automate much of your server database maintenance. The basic syntax is examined here, but as you can see by the number of parameters, manual configuration of SQLMaint.exe can be a little tricky:
sqlmaint [-?] | [ [-S server] [-U login_ID [-P password]]
{ [ -D database_name | -PlanName name | -PlanID guid ]
[-Rpt report_file [-DelTxtRpt time_period] ] [-To operator_name]
[-HtmlRpt report_file [-DelHtmlRpt time_period] ]
[-RmUnusedSpace threshold_percent free_percent]
[-CkDB | -CkDBNoIdx] [-CkAl | -CkAlNoIdx] [-CkCat]
[-UpdSts] [-UpdOptiStats sample_percent]
[-RebldIdx free_space] [-WriteHistory]
[ {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
{-BkUpMedia {DISK [ [-DelBkUps time_period]
[-CrBkSubDir ] [ -UseDefDir ]]
| TAPE }}
[-BkUpOnlyIfClean]
[-VrfyBackup] ] } ]
In this syntax,
-S, as with the other utilities, is the name of the server you're running against (or server\instancename for a named instance).
-U is the login name you're using to run the utility.
-P is the password for the login name you just specified. If you leave off the -U and -P option, a trusted (Windows Integrated) connection is made to SQL Server.
-D specifies the name of the database you are maintaining.
-Rpt is the parameter that identifies where to put such information as the output file reporting errors from running this utility. It should be a full pathname and filename.
-To is the name of an email account to which you want to have a copy of the report sent. You will examine email integration (called SQLMAIL) on Day 19.
-HtmlRpt is the parameter that identifies where to put such information as the output file (in the form of a Web page) reporting errors from running this utility. It should be a full pathname and filename.
-RmUnusedSpace is the parameter that tells SQLMaint.exe to remove any free space on databases larger than the threshold_percent parameter if the database is set to grow automatically (the default). The database shrinks, leaving some percentage of free space, specified in the free_percent parameter. Therefore, if you had a 100MB database, but only 50MB were used, and you had a free_percent of 10, the database would only shrink to 55MB (because 10 percent of the 50MB used is 5MB, the database would have 5MB of free space remaining).
-CkDB | -CkDBNoIdx parameters run the DBCC Checkdb command. This command is examined further on Day 7, "Backing Up Your Databases."
-CkAI | -CkAlNoIdx parameters run the DBCC Checkalloc command. See Day 7 for more details.
-CkTxtAl is the DBCC Textall command. Again, see Day 7.
-CkCat is the DBCC CHECKCATALOG command. Is this getting familiar?
-UpdSts runs the Update Statistics command. You'll examine statistics on Day 13.
-UpdOptiSts runs the Update Statistics command with an optional parameter. You'll examine statistics on Day 13.
-Rebldldx runs the DBCC DBREINDEX command to re-establish your fillfactors on your indexes—again, Day 13.
-WriteHistory records that the maintenance plan was run in a system table in the MSDB database (the sysdbmaintplan_history table).
-BkUpDB | -BkUpLog is the place where your backups will be placed. You can back up either the full database or just the transaction log. Backups will be examined on Day 7.
-BkUpMedia indicates that you're backing up to either disk or tape.
-DelBkUps indicates how long you want to keep your backups if you save them to disk. The time_period is specified as number[minutes | hours | days | weeks | months].
-BkUpOnlyIfClean indicates that the database should be backed up only if it's not corrupt. This is the most powerful feature of this utility and is discussed further on Day 7.
-VrfyBackup verifies that the backup is readable after it has been made. Again, this parameter is examined on Day 7.
For example, you could run
sqlmaint -S rhome -D master -CkDB -Rpt d:\maint.txt
to get a DBCC CHECKDB command run in the master database for your default instance of SQL Server, with a report placed in the file main.txt in the root of your D drive.
NOTE
We're not trying to hide anything; it's just that most of these options will mean a lot more to you when you're finished reading the book than they do right now. That's why we keep saying "You'll learn more about this on Day x."
SQLDiag.exe
The SQLDiag utility prepares for a call to SQL Server Product Support. SQLDiag gathers your error logs, your server configuration information, and the version of SQL Server (including any service packs that have been applied), operating system information, computer system information, and other useful troubleshooting information, and places it all into a single file. This file, named SQLDIAG.txt, is placed in your \mssql\log directory by default.
sqldiag [-S server] [-U login_ID] [-P password] [-E] [-O outputfile]
In this syntax,
-S, as with the other utilities, is the name of the server you're running against (or server\instancename for a named instance).
-U is the login name you're using to run the utility.
-P is the password for the login name you just specified. If you leave off the -U and -P options, a trusted (Windows Integrated) connection is made to SQL Server.
-E specifies that you want to connect using integrated security.
-O specifies the pathname and filename that you want the report to go to.
Print out this text file (or copy it somewhere safe) because it contains so much valuable information about your system. Running this utility and having the information available to Microsoft's Product Support team will speed up any support call you make.
BCP: In a Class by Itself
BCP stands for the Bulk Copy Program. This utility loads data from a file into SQL Server or exports data from SQL Server to a file. You'll learn the details of this utility, including its syntax, on Day 19.