In the first article of this short series covering new and improved utilities of SQL Server 2005 Beta 2, we presented the SQL Server Management Studio, which provides a single, uniform interface for the majority of administration and programming tasks. We also discussed SQL Profiler and Database Tuning Advisor, which, while operating independently, can be invoked from its Tools menu. Now it is time to review the remaining graphical and command line programs, which should be part of the toolkit of every SQL Server 2005 database administrator and programmer.
SQL Computer Manager
This utility, accessible from the Microsoft SQL Server 2005 group in the Programs menu and implemented as Microsoft Management Console snap-in with functionality based on Windows Management Instrumentation, combines features present in three distinct SQL Server 2000 utilities (represented by three nodes in its tree pane) - SQL Server Manager (replaced by the Services node, which includes such entries as Full Text, Microsoft SQL Server Analysis Services, MSDTC, MSSEARCH, ReportServer, SQL Agent, and SQL Server), Server Network Configuration, and Client Network Configuration (both nodes are labeled the same as their predecessors). SQL Computer Manager is backward compatible with SQL Server 2000 and 7.0 and supports older versions of Analysis Services and Reporting Services.
This command-line utility serves as a new, improved version of OSQL and ISQL programs available in earlier versions of SQL Server (they are also available in SQL Server 2005 Beta 2, but since they are deprecated, their use is discouraged). It allows you to connect to any instance of SQL Server via OLE DB (rather than via older technologies such as DB-library and ODBC, used by OSQL and ISQL, respectively) and run batches of T-SQL statements. Its additional benefit is the ability to connect to a server via a Dedicated Administrative Connection (by including -A switch when invoking it from the Command Prompt), which comes handy in case other means of connectivity fail (e.g. when server is locked due to its abnormal state). This is possible since a certain amount of resources is reserved for it every time SQL Server is started.
SQLCMD.EXE has a number of functional enhancements, accommodating the creation of more elaborate scripts, which can significantly simplify automation of a majority of administrative tasks. In particular, the new features allow you to accomplish the following:
- Prior to connecting to a SQL Server instance using SQL Server authentication, it is possible to set the SQLCMDPASSWORD environment variable (instead of specifying the password with the -P option), which is applied for the duration of the subsequently invoked session (similar to OSQLPASSWORD used with the OSQL utility). This eliminates the need for hard-coding the password into batch files. As with OSQL and ISQL, by default, in absence of this environment variable or -U and -P options, a trusted connection, relying Windows Authentication is attempted (or if -E option is used explicitly).
You can assign values to custom and predefined scripting
variables to be used within scripts invoked from the command line. Custom
variables are set either with -v switch (as part of SQLCMD syntax) or with the setvar
command (within a script), while predefined variables have their designated
names and switches. SQLCMD.EXE also has the ability to reference environment
variables (which you can list by typing SET at the Command Prompt) as long as
they have not been defined using the previous two methods. Among the more
useful predefined variables are:
- SQLCMDSERVER and SQLCMDDBNAME - set with -S and -d switches, designate the target SQL server and database (respectively) to connect to.
- SQLCMDWORKSTATION - set with -H switch, assigns the name that will appear in the hostname column of the sys.processes catalog view (default is the computer name from which the session is initiated).
- SQLCMDUSER and SQLCMDPASSWORD - set with -U and -P switches, determine the credentials to be applied for connection to a target server (assuming that SQL Server authentication is used).
- SQLLOGINTIMEOUT and SQLCMDSTATTIMEOUT - set with -l and -t switches, define the number of seconds that it takes before a login to OLE DB provider or a SQL statement execution (respectively) time out.
Custom scripting variables are designated within a script by $ prefix and enclosed in a pair of parenthesis. For example, a simple script listed below contains four of them:
USE $(dbName) SELECT * FROM $(TableName) WHERE $(ColumnName) = $(ValueName)
In order to assign their values you could invoke the SQLCMD utility with the following syntax (for the sake of our example, let's assume that the script is saved as "c:\Documents and Settings\dbo\scripts\RunMe.sql"):
SQLCMD -E -v dbName="TVh3ll" ColumnName="sID" TableName="dbo.Teletubbies" ValueName="Twinky-Winky" _ -i "c:\Documents and Settings\dbo\scripts\RunMe.sql"
(Note that the entire command needs to be typed in on a single line). Entries following the -v switch are used to associate values with script variables and the file system path after the -i switch specifies the location of the script, (you can provide multiple file names that will be processed in sequence). If you want to store the output to a file, use the -o switch along with its name. You could also set a value for each variable (or selected variables only) using the setvar command (preceded by the single ":" character) within the script (or specify a target server by applying :connect command, followed by the server name). In such a case, our script would change to:
:setvar dbName "TVh3ll" :setvar ColumnName "sID" :setvar TableName "dbo.Teletubbies" :setvar ValueName "Twinky-Winky" USE $(dbName) SELECT * FROM $(TableName) WHERE $(ColumnName) = $(ValueName)
While our example is rather trivial, it illustrates the main advantage of using parameters - they make code easily reusable. You can rerun the same script with a different set of values by either changing the command line used to invoke it or by altering its header. On the other hand, if you have to run a simple query, you can specify its content, enclosed in double quotes following the -Q or -q switch (depending on whether you want to exit SQLCMD or not after the query completes).
Note that, as we mentioned before, it is possible to execute SQLCMD scripts within Query Editor by switching it to SQLCMD mode (which, behind the scenes, relies on .NET SqlClient, rather than OLE DB, like its command-line counterpart).
Other Command Line Utilities
Among other command line utilities introduced or improved in SQL Server 2005 are the following:
- SQLWB.EXE - launches SQL Server Management Studio from the Command Prompt or Start -> Run text box. Through its switches, you can specify which type of server (-t S, -t A, or -t C for SQL Server, Analysis Server, or SQL Server Mobile Edition, respectively), server name (-S), and database (-d) you want to connect to, provide authentication information, or designate which queries, projects, or solutions to open (-i filename). The defaults (in absence of switches) are defined in the Tools -> Options menu of the SQL Server Management Studio.
- Profiler90.EXE - launches SQL Profiler from the Command Prompt or Start -> Run text box. It has the ability to connect to a specific server (/S and /A followed by a server name will allow you target SQL or Analysis Services Server) and database (/D), to load a desired trace table (/B), template (/T), or file (/I), and write results to a file (/f).
- DTA.EXE - operates as a command line version of Database Tuning Advisor, analyzing performance and providing recommendations regarding its improvements (such as the addition or removal of indexes, indexed views, or partitions). You have an option of running it on the same server you analyze or a separate one (-R switch) in order to offload the processing burden. Connection settings are specified using the same switches as the ones present in the previously described utilities. Others include information specific to the DTA functionality, such as tables to be tuned (-T switch), workload in the form of SQL Profiler trace, SQL file, or SQL Server trace file (-i switch), workload trace table (-t switch), or maximum time spent on tuning (-A switch).
We will cover other tools introduced in SQL Server 2005 Beta 2, such as lrtest (Language Resource Test) or DTEXEC and DTUTIL when covering Full-Text Search and Data Transformation Services.
Business Intelligence Development Studio
Primarily a development utility, this program is intended for creating Business Intelligence solutions, integrating projects involving Analysis Services, Reporting Services, and Data Transformation Services. Similar to SQL Server Management Studio, its interface is solution oriented, with each solution grouping individual projects, such as reports (utilizing Reporting Services) or DTS packages. The tool also provides integration with Visual SourceSafe. We will be presenting this tool, as well as others related to Analysis, Reporting, and Notification Services in more details in future articles of this series, when dealing with each of these areas of SQL Server 2005 functionality.