SQL Server Tools and Utilities

BCP

BCP (bulk copy program) is a tool that is used to address the bulk movement of data. The utility is bidirectional, allowing for the movement of data in and out of a SQL Server database.

The SQL Server 2000 version of the Bulk copy program (BCP) utilizes the ODBC bulk copy API instead of the DB-LIB API that was used in earlier versions of SQL Server. The ODBC bulk copy API was used to support new data types that the DB-Library API does not support. Backward compatibility options are provided with the SQL Server 2000 BCP utility to allow bulk copy of data types supported in earlier versions.

BCP uses the following syntax:

BCP {[[database_name.][owner].]{table_name| view_name} | "query"}{
   in | out | queryout | format} data_file
[-F first_row] [-L last_row]
[-t]
[-E]

ISQL/OSQL

The ISQL and OSQL command-line utilities can be used to execute SQL statements, stored procedures, and script files from the command prompt. Commands that can be entered interactively via Query Analyzer can be placed in batch files and executed via ISQL/OSQL. In addition, the output from these files can be routed to a file that allows daily reports and tasks of this nature to be run unattended.

The ISQL utility uses DB-LIB to communicate with SQL Server, whereas OSQL uses ODBC. Because ISQL is based on the DB-LIB interface, it does not support Unicode data. DB-Library has not been enhanced to support any of the new features in SQL Server 7.0 or 2000 and is only maintained for backward compatibility. Microsoft recommends that you use OSQL instead of ISQL for any command-line procedures.

The command-line parameters for ISQL and OSQL are similar with only a few differences. The command-line parameters for both utilities allow you to specify the query or file you want to execute, the target server and database, as well as the location for the resulting output.

ODBCcmpt ODBCcmpt allows you to enable or disable the 6.5 or 7.0 ODBC compatibility option, using the following syntax: ODBCcmpt filename The filename is the name of an application's executable file for which you want to set the compatibility level. This option permits the SQL Server ODBC driver (Version 3.7) to be compatible with previous drivers. You might need to enable this option when running an application that SQL Server 2000 should treat as a 6.5-level or 7.0-level application. This allows backward compatibility for applications using either the ODBC 2.x or ODBC 3.x API. The ODBCCMPT.EXE file is located, by default, with the SQL Server tools in Microsoft SQL Server\80\Tools\Binn\.

odbcping The odbcping utility is a good diagnostic tool that allows you to test a client machine's ODBC connectivity to your database server. It uses this syntax: odbcping { -Sserver_name | -Ddata_source } Unlike makepipe and readpipe, odbcping runs only on the client. You can use odbcping whenever you need to verify that a client's ODBC connectivity is properly configured. You can also incorporate the use of odbcping as part of an installation procedure. You can run odbcping in two ways. You can execute odbcping-Sserver_name to test a client's direct connectivity to SQL Server. You can also run odbcping-Ddata_source to use the specified data source to connect to SQL Server. In either case, if successful, odbcping displays the version of SQL Server and the version of the SQL Server ODBC driver. The following example shows a simple odbcping execution (using the SQL Server name) and the resulting output: >odbcping –Swin2000svr\instance1 CONNECTED TO SQL SERVER ODBC SQL Server Driver Version: 03.80.0380 SQL Server Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 4.0 (Build 1381: Service Pack 6) Query Analyzer Query Analyzer is a fairly straightforward and simple tool to use. Since the user interface is user-friendly and easy to navigate, the purpose of this section of the chapter is not to provide a basic tutorial on using Query Analyzer, but to help you get more out of Query Analyzer by pointing out some of its more useful features that you might not be aware of. Establishing Connections When Query Analyzer first starts up, you are presented with a connection dialog as shown Within this dialog, you can choose to connect using Windows authentication or using a SQL Server specific login ID and password. The SQL Server: field provides a drop-down list of the servers you've most recently connected to, or you can simply type in the name of the SQL Server. If you click on the button to the right of the server name box, it will display a list of SQL Servers that it detects are currently running on your network. If you have the appropriate administrative privileges, a check box is available that will allow you to automatically start the specified SQL Server if it is not running. The SQL Debugger One of the most significant features in Query Analyzer is the SQL Debugger. With the various commands and programming constructs in Transact-SQL (T-SQL), stored procedures can perform some pretty complex operations. You might have as much programming logic built into stored procedures as in some of your other application routines written in other programming languages. As you develop and test your application code written in Visual C++ or Visual Basic (or whatever programming language you might be using), it is likely that you will use a debugger to step through the code and verify that it is working correctly or to identify where the logic is breaking. When your stored procedures are not executing correctly or they are returning the wrong result, you need to debug them as well. In the old days, the only way to debug stored procedures and step through them line by line was to rewrite them as SQL batches and execute the statements individually or in small groups. The problem with this approach was that the contents of local variables didn't carry over between batches, so you had to redeclare and reinitialize the variables for each batch and set the values to what they were at the end of the previous batch. This process was tedious, and the SQL statements would optimize differently when passed a variable rather than what would have been a stored procedure parameter. For more information on how queries are optimized, see Chapter 35, "Understanding Query Optimization." The only other way to track what a stored procedure was doing was to litter it with print statements to display the status of the procedure and the contents of local variables and parameters at various points. I remember using this approach one time to debug what looked like a simple problem. I had a stored procedure that needed to delete a parent record, and then all related child records. Everything worked just fine by using local variables and separating each SQL statement. My print/debug statements didn't shed any light on the matter, either. After spending about half a day trying to track it down, I finally identified the problem—I was missing a parameter to the stored procedure that was being called within the main procedure, shifting the parameter values over one and causing the wrong value to be used to find the matching rows. (I could have avoided this by passing the parameters by name rather than position. Live and learn!) If only I had had a debugger available to debug the stored procedure, I could have identified the problem in a minute instead of a day. The First T-SQL Debugger Stored procedure debugging was available for SQL Server prior to SQL Server 2000, but it wasn't part of the SQL Server installation. Since the release of Visual Studio 6, Visual Interdev has included a T-SQL Debugger. Prior to this, it was available inside Visual Basic Enterprise 5.0 and Visual C++ Enterprise 4.2. However, getting the debugger up and running was an arduous process. You had to have all the following pieces in place: You had to have the Enterprise Edition of Visual Studio installed. You had to be running at least SQL Server 6.5 with Service Pack 2 or later. The server running SQL Server had to be running under Microsoft Windows NT 4.0 or later. The client workstation had to be running Windows 95 or Windows NT 4.0 or later. In addition, for SQL Server versions prior to SQL Server 2000, the debugging components were not installed on the server by default. To get the debugger to work, these pieces had to be installed manually. This caused a lot of headaches: Programmers who wanted to use stored procedure debugging had to bother the administrator, who might or might not have seen the need to install these pieces. It's a rare administrator who will upset the delicate balance of his smoothly running SQL Server to indulge the whim of a developer, unless some sort of bribe is involved. You also needed to ensure Distributed COM (DCOM) was installed properly on the server and the client. You had to make sure the DCOM configuration on a Windows 98/Me machine supported debugging. The main problem with using this debugger was that you had to install Visual Interdev. This was a pretty large, involved install if all you wanted to do was use a SQL Debugger. Also, the debugger was not part of a standard query tool like Query Analyzer, nor was it a standalone tool. It had to be brought up via a Data View window in Visual Studio. People needed a T-SQL Debugger that could be installed along with the SQL Server Client Tools that was integrated with Query Analyzer. That finally came along in SQL Server 2000. Using the T-SQL Debugger in Query Analyzer Query Analyzer that ships with SQL Server 2000 now includes an integrated T-SQL Debugger. This is a full-featured debugger that lets you step through stored procedure code a line at a time, set breakpoints, view the contents of local and global variables, and see the results of the T-SQL statements as they execute.