SQL Server Reference Guide

In a previous tutorial I explained that there are a few methods to access your SQL Server. If you're a developer you'll use several tools, but if you're the system administrator you'll use three primary tools to manage the server from day-to-day. In this tutorial, I'll focus on those tools for SQL Server 2000.

Enterprise Manager

You can manage Microsoft’s SQL Server in two main ways: Graphically and Command-driven. Enterprise Manager is the graphical way to manage SQL Server, and with it you can do the following:

• Connect to many different SQL Servers
• Access and edit a server’s properties such as memory and security types
• Access and edit a database’s properties such as file location, size, and other options
• Create a database
• Create all database objects such as tables, indexes,
• Manage users and security
• Create and edit replication
• Create and edit an automatic maintenance plan
• Watch server activity such as locks and even SQL statements
• Import and export data from several sources to many destinations, for instance from Excel to Oracle, or from Oracle to SQL Server
• Check the SQL logs (information logs, not database logs)

If you’ve ever used another tool to manage a database system (as I have), you will really appreciate the work Microsoft put into SQL’s Enterprise Manager. Oddly enough, the Oracle tool that manages their database is also called Enterprise Manager, but I’ve used both and the Microsoft tool is more user-friendly. Enough about how much I like the tool, let’s take a tour.

What we’ll be doing is looking at what the tool does, not how it does it or what each options means. I’ll leave that for the other articles on this site – make sure you use good use of the search features on this site. There’s a wealth of information here.

Enterprise Manager is installed automatically if you’ve installed any SQL Server edition other than MSDE. You can also install Enterprise Manager on a workstation by installing the "SQL Tools" option from the SQL Server CD.

Registering a Server

If you’ve installed full-up SQL Server (meaning you chose all options during an install on a server machine), your Enterprise Manager will have the server’s name already in place. If you’ve just installed the tool (perhaps on your workstation), you’ll need to "register" the server. All that means is that you type in the name of the server and the security credentials to access the server.

Find the Enterprise Manager tool on the Windows Start button under Programs | Microsoft SQL Server | Enterprise Manager. Once you launch it, you’ll see the following screen:

Figure 4

You’re presented with a Microsoft Management Console (MMC) that displays the groups the servers have been placed in. The default group is called SQL Server Group, although you can make more. Double-click that group and you’re presented with the following screen:

Figure 5

You’ll notice that there are no servers registered at this time. That’s because you’ve installed the tool locally, so Enterprise Manager doesn’t know which server you want to connect to. It’s pretty simple to connect to the server, and this process shows you the paradigm you’ll use for the rest of this tutorial. You can right-click an object in the left-hand pane, and you’ll see the functions you can perform on that object. Each object has different "verbs" associated with it.

Right-clicking the SQL Server Group object allows you (among other things) to register a server. This action will bring up a wizard that will guide you through connecting to a server. You can use the name of the server (which is the same name as its Windows name) or you can also use the TCP/IP number, if you’re using that protocol. In this way you can control a server from thousands of miles away.

You’ll be asked to provide a name and password to access the server, or whether you’d like to use your current Windows account to connect to the server. Once you connect successfully to a server, you’ll see a screen similar to the one shown here:

Figure 6

Your view may look a little different than the one I’ve got shown here. The view on the right-hand panel is called a Taskpad view which is basically an HTML control area. I like it a great deal, especially for administrators new to SQL Server because it provides many wizards and rollover functions. To set your view to be similar to mine, right-click the Server object and select View | Taskpad. Take a few moments and explore the options you have available with this view. If you don’t find this one useful, there’s an icon view, a details view and more.

Server Properties

Right-clicking the Server object and selecting Properties brings up the panel shown here:

Figure 7

This panel has many tabs that control the behavior of the entire SQL Server installation. Later I'll show you what each option means and how to set it; for now let's concentrate on where it is.

The first tab you see is the General area, where you set options such as startup and error reporting parameters. Normally you don’t change much here.

The next tab is Memory, where you can set whether SQL Server takes a fixed amount of RAM (and how much) or a variable amount.

The Processor tab gives you the option of using a particular number of processors in a multiple processor system, and whether to boost the priority of SQL on a system.

The Security tab sets the option of using only Windows accounts for SQL Server access or whether SQL will use both Windows and its own security tracking for access.

The Connections tab sets various SQL connection parameters such as ANSI levels and so forth, and can also be used to limit the amount of connections the server will listen to.

The Sever Settings tab sets the language, Mail and two-digit year support the server will use. The Database Settings tab changes some database-wide options such as a default location and backup directory, as well as timeout and fill-factor options. Finally, the Replication tab sets up a server to be used in Replication.

Lots of options - don’t worry if you don’t understand what each of the options does, just remember for now that this is where you set them.

Databases

Next in line is the Databases object. Double-clicking this object brings up the view shown here:

Figure 8

Again, I’ve got my view set to Taskpad. I find the Taskpad view even more useful on the database object, since in addition to various wizards, it also displays information about the database backups and size. Notice that I’ve selected a database for this view, and right-clicking the database name gives me options from backing up and restoring the database all the way to the database properties. I’ll select that option, and the database options panel opens up. The first tab shows information about the database.

The second tab on this screen shows the location and size options of the database. Files can be added to a database to spread the usage of the hard drive, but tables and other objects won’t use those files unless you tell them to go there on creation. You can also alter the objects later to reside on these other files. The size option sets an absolute size or allows the database to grow automatically.

The third tab shows the same information for the database logs. These are the files that are used to store data before it’s actually written to the database files. This feature (found in most large-scale databases) is a core paradigm in keeping data safe and making entry fast.

The fourth tab in this panel shows the Filegroups for the database. Filegroups are logical names used in conjunction with physical files, allowing for ease of coding when you’re creating or altering objects such as tables and indexes.

The fifth tab shows a lot of options for the database, and each of them has uses, some of them in conjunction with each other. If you’re interested in what each of these do, just open up this tab and press F1 to get context-sensitive help. Even knowing what each option does isn’t enough, there are strategies based on the program that accesses the database to consider when setting these options. I’ll cover more of these options in articles throughout this site.

The last tab is the Permissions tab, where you set database-wide permissions. This tab isn’t used to set all the permissions needed for proper access, but it is part of the process.

Closing out the Properties panel brings you back to the database you’ve selected. Notice that there is a plus-sign next to the database name, as shown in the last graphic. Clicking that (or double-clicking the database name) brings up the objects within the database. While I won’t cover each object here, one item to note in particular is the Tables object. This object is a bit different from the others in that one of the right-click options is to query the table and return the data. While this is a great thing to be able to do, you have to be careful with this option as it takes locks on the data that you may not be aware of. A good rule of thumb is to minimize viewing data this way on a production database.

Spend a little time with the help features (Press F1) on each of the objects to learn more about them. I’ll also cover these objects further in the Programming SQL Server section later.

Data Transformation Services

Data Transformation Services is SQL Server’s import and export facility. Not only can you import and export data into and out of SQL Server, but this unique tool can connect to several data sources, from text files to Microsoft Access, Microsoft Excel and Oracle data. If your provider isn’t natively supported, you can use ODBC sources as well.

The Data Transformation Services object holds Packages, which are saved sets of these imports and exports. Each time you right-click a table and select Import and Export Data, you start a wizard which builds these packages. If you save the package at the end of the wizard, it will show up here. You can also create these packages from scratch, using this object.

Along with the Packages item, other objects include Meta-Data information about the package. As you’ll learn later, these packages can be stored in several ways, including in SQL Server or as a file, or in the Meta-Data services provided by SQL Server.

Management

The Management contains other objects that are worth discussing. The first of these is actually another part of SQL Server – it’s the SQL Server Agent. This program is a service that runs on the server that performs all scheduled activities in SQL Server. It’s an incredibly powerful tool that has jobs which contain the instructions to implement and their schedules; operators who can be told about the job status; and alerts that contain messages or actions which can be raised on certain events. The SQL Agent needs to be configured with the mail address and other information to notify those operators of events.

The next item in the Management object is Backup. As you might expect, you can manage your backups here, but you can also use this area to create backup Devices, which are really just friendly names for file locations or tape drives and the like. These are useful to make the backup process dependent on an abstracted layer so that scripts don’t have to change if the backup locations do.

Moving down the items in the Management list is the Current Activity sub-object. This object lets you see the locks, blocks and other activity metrics currently happening on your databases. The one important thing to note here is that this view is static – you need to right-click the object and select Refresh to renew the information.

The next item is one you should become familiar with right away - the Database Maintenance Plans. This sub-object provides the easiest way to check the status of your databases, as well as optimization and backup tools, all automatically. These plans are fully customizable and can send reports to an operator. When I first get to a client’s site, I check to see if they are using some sort of manual maintenance scripts or a Maintenance Plan. The Maintenance Plan has a great history tool that you can use to see if any of the plans have failed.

The final item under the Management object is SQL Server Logs. There are seven logs, the current one and six others, which track everything that happens on SQL Server since the server was started. You can change that number if you wish by right-clicking the SQL Server Logs object and selecting Configure.

Each time you start SQL Server, the first log moves down and a new log is started. These logs are just text files, but they are an invaluable place to look to debug problems or track activity. Understand that these logs don’t contain every SQL statement that is run, just server-level messages.

Replication

The next object on the tree is Replication. SQL Server can send all or parts of data from a database to another SQL Server. This transfer of data can be scheduled or on-demand, pushed out or pulled from, and one-way or bi-directional. To do all this, SQL Server uses a publish-subscribe metaphor, and this object is where you manage that.

Security

SQL Server uses two accounts to get to data securely. The first is a SQL Serve logon, the next is the database login. You can think of a set of keys to your office building. You probably need a key (or code) to get into the building, and another to open your office. In reality, these SQL accounts are usually the same name, and can also be Windows accounts or SQL accounts. It’s quite simple to use this object to set up both the server and database accounts.

The next item under this object is the Server Roles sub-object. Server Roles are another security element that has server security settings combined into a single place, sort of like groups in an operating system.

The last two items in this object involve connecting to other servers as if they were local. The difference in a linked server and a remote server is that the remote server is used to have stored procedures run on another SQL Server on behalf of a client. Linked servers can do a bit more, in that they can run full queries on remote systems.

Support Services

The Support Services object contains three items. The first is the control program for the Distributed Transaction Coordinator. For certain network-level programming constructs and for anything that uses Microsoft’s COM+ to talk to a database, this program is the layer that controls the transactions.

The next item here is the Full-Text sub-object. SQL Server has the ability to search and query text fields in the database, but only if their indexes have been built using this tool.

The final item in this object is SQL Mail configuration. SQL Server has two mail facilities – one in SQL Server Agent, which allows the scheduled tasks to send mail, and SQL Mail, which allows SQL Server to send and even respond to e-mails. You can configure the latter here.

Meta Data Services

The last object is one not normally accessed by most DBA’s. Meta-Data is data stored about other data. For instance, data is stored in a table, but the structure of the data such as the columns and so forth are Meta data. SQL doesn’t store that particular bit of information here, but it may help you understand what Meta data is. You can store Meta data in SQL Server, usually programmatically.

We’ve only looked at the objects in SQL Server Enterprise Manager as well as how it operates. This should be one of the first topics you learn before you move into the other tasks you’ll see on this site.

Query Analyzer

One of the things I like the most about working with SQL Server is that you can manage it in two ways – graphically and via commands. You might be thinking that isn’t too special, but understand that everything you can do with the graphical tools you can do with a command – if you know how. In this tutorial I’m going to give you an overview of how this tool works and what you can do with it.

Just as we discussed with the osql and Enterprise Manager tools, you can install Query Analyzer in one of two places – either on the server or on a workstation. If you have SQL Server installed on your system, you already have the tool on that box. If you’re using your local workstation to manage SQL Server, you’ll need to install the SQL Tools to have this product available.

Either way, you’ll need to start by connecting to a server. When you start Query Analyzer (either from the Start Menu or as a Tools option in Enterprise Manager) you’ll be presented with a box for server name and credentials, like this:

Figure 9

If you’re running this tool on the server, you can type in a period for the server name, or the word (local) with the parenthesis and all. If you’re using Query Analyzer on another system or you want to connect to another server, type that server’s name, or the IP address of the server (if your machine and the server are using TCP/IP).

For the credentials, you have a couple of choices. This choice depends on how the server is configured. SQL Server can use its own security mechanism or it can look to Windows to provide the security.

Assuming that the server you’re connecting to is set to allow Windows authority and you’ve been granted access, just check the box marked Use Trusted Authentication and you’ll be shown the following screen:

Figure 10

Let’s take a quick tour of the parts of the tool. Across the top you’ll see the name of the server and database you’re connected to. Since you can have several of these windows open at one time, this will become handier than you might first think.

The next section is the menu bar. We’ll examine some of the command highlights later. As with all Microsoft products, you can access the menu with the mouse or with the ALT key.

The next section is the icon bar, where you can hover with your mouse pointer and see what the commands do.

The next section you see is on the left, and is new for SQL Server 2000. It’s called the object browser, and it’s really handy. If you don’t see it on your system, press F8. It allows you to see the various databases, tables and other objects on your server. The really useful part is that you can right-click on these objects. When you do, a menu will pop up that will write scripts for you. It will put these scripts in the next part of the screen on the right. Speaking of scripts, you may have noticed the tab at the bottom of this side of the screen called Templates. Selecting this tab brings up several pre-formed scripts that you can use to create databases, control security and more. To customize the scripts you see and replace the parameters with your information, press CTRL-SHIFT-M.

You can even create your own scripts that will show up here. Examining these pre-made scripts is an excellent way to learn Transact-SQL (T-SQL), the language of SQL Server.

This right-hand side of the screen is the command area. Typing in a command (or scripting one as I mentioned earlier) will show up in this side. As you type, you’ll see, as you type commands they will turn various colors. These colors actually have a meaning:

This color-coding helps you debug commands to correct any spelling errors.

Another cool feature of this panel is that you can highlight a command and press SHIFT-F1 to see the context-sensitive help for that command.

Once you’ve type the commands, you can run them by pressing F5, or clicking the green triangle in the icon bar. An interesting side note is that you can highlight a group of the commands, and when you press F5 only the highlighted commands will run.

Running the commands will split the right-hand part of the screen into two parts. The top part still contains the commands you typed, and the bottom part will contain the results of those commands. Not only that, but the bottom part will contain two or more tabs. The first tab is the results of any queries, and the other tab contains messages from SQL Server involving the commands. There might be more tabs, which we’ll examine in a bit.

Figure 11

This results pane at the bottom of the screen can be set up a few ways. You can have the results in plain text format, a grid, and other formats. You can change the output by using the menu bar and selecting Query and then Results to text, Results to grid or Results to file. There are also keyboard shortcuts for this feature – text is CTRL-T, Grid is CTRL-D and file is CTRL-SHIFT-F.

One final note about both of these panes – you can right-click inside either and save the text in them as a file.

Now that we’ve seen the way the tool works, let’s try a few examples. You can learn more about the Transact-SQL (T-SQL) statements elsewhere on this site, so just follow along for now. If you’re really curious about them, use the trick you learned earlier by highlighting the command and pressing SHIFT-F1.

If you haven’t already, open your Query Analyzer tool and connect to your server. You’ll notice on the icon bar the name of a database. You can use this pull-down menu to set the database you want your commands to operate against. There are other ways to do this, too. For now, pull down the menu and select the pubs database. If you don’t have a pubs database, connect to another server.

Now that your database is set, type in the following commands:

SELECT * FROM authors

Notice that the SELECT and FROM parts change color as you type. Change the word FROM to FRO and you’ll notice the color changes again. Press CTRL - F5. This command doesn’t really run the SQL, but will return whether the syntax would work or not. This is a really useful tool to make sure that your syntax is correct before you update 50000 rows of data with one command!

Correct the text to what I've got above and press F5. As you can see, the results are returned to a grid. One question commonly asked is, "can you edit the grid contents and have that saved to the database?" The answer to that is no.

If you read the earlier tutorial on Enterprise Manager (the graphical management tool) you’ll see that I suggested that even though you can return data with that tool, you should use this one. Enterprise Manager takes locks that allow you to directly edit the data, but Query Analyzer does not. If you’d like to edit or add data, you’ll need to learn the T-SQL to do that.

Now that you’ve run a simple query, let’s use one of the other features I mentioned earlier. In the Object Browser on the left side of your screen (press F8 if you don’t see it), double-click the pubs database. Now double-click the User Tables item and then locate the table called dbo.authors (dbo is the name of the owner, authors is the table name). Right-click that table name and then select Script Object to New Window As... then SELECT.

The command that is returned is different than your earlier query – although they do the same thing. Earlier we used a command (SELECT * ) that selects every column in a table and this command selects each column individually. The advantage to this model is that you can select only the columns you want, in the order you want. Now find the word [au_fname] and just in front of that press ENTER, moving it to its own line. Press F5.

I want to point out three things during this process. The first is the scripting options in the Object Browser. I think you’ll find it an invaluable tool, even once you’re familiar with T-SQL. The next thing to notice is that you’ve got two windows open – you can tell that by looking in the status bar at the bottom of the screen that says Connections – 2. That’s important because it’s easy to get lost when you do a lot of automatic scripting. You want to make sure that connections don’t interfere with each other. The final thing I wanted you to see is that white space doesn’t matter to Query Analyzer. You could have each word on its own line, or all on one. As long as you don’t break the word up, the commands will work.

Well, we’ve taken a look at the major use of this tool, but there are others. I’d like to touch on a couple more, the Show Execution Plan, Show Server Trace and Show Client Statistics.

The first option, Show Execution Plan, becomes priceless when you’re a developer trying to structure your T-SQL to be the fastest possible. This command (invoked from the Query menu), adds another of those tabs I spoke about in the results pane.

Try it now, and execute the same query you did earlier. Once you select the Execution Plan pane, hover with your mouse pointer over one of the graphics. In another article I’ll cover the proper use of this information, but if you can’t wait, then press F1 while you’re in here.

The next option, Show SQL Server Trace, also adds a tab. Try that now as well. You’ll see exactly what happens from the server’s point of view, which can be quite useful in debugging stored procedures.

The final option I’d like you to see is Show Client Statistics. This information shows how long it took for the client to get the results back. While this information is useful, make sure you run this on the real client – running it on the server might not be representative.

The last thing I’d like to talk about in this tool (and believe me there’s a lot more) is the Tools, Options area. It’s important to be familiar with these options, since they can affect the results and in extreme cases even the data in a database. Unless you’re told by your developer to change these options, you don’t need to, but there is one that I always set on my systems.

Move to the Results tab and locate the Maximum characters per column. It’s set by default to 256, but I usually change that to around 4096. This allows you to see all the results, even if they are longer than 256 characters.

Well, I hope I’ve at least piqued your interest in this tool – one of the best editors I’ve seen. We’ll use more of its handy features (like block indents) in weeks to come.

OSQL

I’ll be honest. I’ve been taken to task for writing about this subject before – for considering it important enough to explain. But I think if you bear with me, you’ll see that a lowly command-line utility can be your Swiss-army knife for solving lots of sticky problems.

What am I talking about? Why it’s osql, Microsoft SQL Server’s command line interface. Why would you want to do this? Well, stick with me and I think you’ll agree that there are lots of reasons to use this tool.

First, let me explain where this tool is. If you’ve installed SQL Server or even the SQL Server client tools, then you have osql lying around on your hard drive. It’s already on the server, and that’s where I use it the most. Most of my examples will be shown from the server perspective, but with just a couple of parameters, they’ll work from a remote system that has SQL Server tools installed as well.

Now that we know where the tool is, let’s see what the tool is. Right from the command shell (the DOS environment or CMD.EXE in NT and higher) you can run SQL scripts and receive output from SQL. If you’re familiar with SQL’s Query Analyzer, osql is very similar, only without the fancy interface.

Not only can you submit and run queries, but any valid TSQL statements. You can create databases and users, alter tables, and insert data.

So now you know where the tool is and what it does. How do you use it? Let’s start right off with an example. From your SQL Server, open a command prompt and type the following:

OSQL –E –dpubs –Q"SELECT * FROM authors"

Once you press enter, you'll see the results of the query stream by on the screen. This assumes, of course, that your server allows you to connect to SQL Server using your current user account and that you have the pubs sample database installed.

Let’s dissect the example we just saw. OSQL is of course the command that starts the whole shooting match. One thing to note as we go forward is that this tool is case sensitive, and we’ll use this sensitivity to our advantage in a bit.

Moving to the right we see the first parameter to OSQL, the –E. This parameter tells SQL to trust the Windows user account we launched the tool under for the security. If your server doesn’t trust your account, or if you’re using SQL authority to connect, you could modify the command like so:

OSQL –Usqlusername –Ppassword –dpubs –Q"SELECT * FROM authors"

This time I added the –U (remember about the case) and then the user name in SQL that is allowed to log in. If this is a SQL account, just use the name itself. If it’s a Windows username, use the domain\username convention. I also added the –P and then the password for that user.

Getting back to the example, the –d parameter calls for the database name. Speaking of that case sensitivity, a –D is a different parameter altogether. The uppercase D means to use a data name, which is the name of an ODBC connection. Just as an aside, the OSQL command uses ODBC drivers under the covers, so the ODBC connections are available for this tool.

Next in line is the –Q parameter. This parameter (followed by some quotes) means "run this query and then get out of the tool". To beat the proverbial dead horse, a lower-case q is something else. It means to "run this query and stay in the tool". Using a lower-case q will leave you in the query window until you type EXIT (Or QUIT or press CTRL-C) and press ENTER.

So now you’ve seen a fairly basic example of the tool. Let’s extend this example a bit.

If you just want to return some data to the screen, then OSQL is certainly an adequate too to use. But most of the time what you’ll need is a way to automate the tool – meaning you’re not at the console when the tool operates. This is often called a "headless" application. You want the tool to accept input and create output automatically. OSQL provides two additional parameters that will enable you to create these features. Let’s look at another example:

OSQL –E –dpubs –i C:\TEMP\INPUT.SQL –o C:\TEMP\OUTPUT.TXT

I've explained the –E and –d parameters, the new ones are –i and –o. The –i parameter specifies a text file that contains Transact-SQL (T-SQL) statements. Something to remember when creating these files (called scripts) is that you need to separate each group of statements (called a batch) with a GO statement. If you’re new to batches, look up the GO statement in Books Online.

You can also have multiple lines in your input file, like this:

REM C:\BATCH\INPUT.SQL
SELECT *
FROM authors
WHERE au_lname = ’White’
GO

Next in line is the–o parameter, which creates an output file. This text file contains the results of the queries and commands.

These are only a few of the parameters you have at your disposal; others do things such as running operating system commands (!!), returning the value you get from a query to the operating system ( EXIT() ), and more. Check Books Online for a description of these parameters. You can also type OSQL -? for a list of these parameters right on the screen.

We’ve got the where, the what, and the how. All we have left to answer is the "why". If you’ve played with the examples so far, then you may have some ideas of your own for the tool’s uses.

You might be thinking that you can do everything we’ve see so far with Query Analyzer. While that’s true (except for the exporting to a file part. Don’t forget that!), it isn’t so much what the tool can do as much as where you can run it from. Sure, the purists will tell you that you run SQL statements from an application or Query Analyzer, but what about that installation of SQL’s MSDE? It doesn’t come with any interface other than osql.

Here are a couple of ways I’ve used the OSQL command.

Problem:

I needed to select data from the database and create a file on a network share from the results every night at 10:00.

Solution:

I created the following batch file called GETDATA.CMD:

@ECHO OFF
OSQL –E –dMyDatabaseName –Q"SELECT * FROM sourcetable WHERE columname = ’condition’" –o\\SERVERNAME\SHARENAME\OUTPUT.TXT NOTE

(if the line above wraps, the –o part should be on the same line with the OSQL command)

I then scheduled that command to run the server (using the Task Scheduler in the Operating System) at 10:00 every night. Now the users who don’t have rights in the database at all are able to get at just the data they need. By default the data is separated with spaces, but you can specify another character with the –s parameter, which could allow you to create a file that would import into another application like Microsoft Excel.

Problem:

On an MSDE system, I needed to provide a way to monitor the system remotely without SQL tools. On a regular system this isn’t a problem, but the customer wanted to have a web page automatically created with information about the state of the SQL Server.

Solution:

I created three files on the server:

The first is START.TXT, made in a directory called C:\CHECK:

<HTML>
<PRE>

The second is END.TXT, also in the directory called C:\CHECK:

</HTML>
</PRE>

And the third is the batch file that creates the statistics of the server:

REM MaintCheck.CMD
@ECHO OFF
CLS

osql –E –Q"sp_monitor" –o c:\check\middle.txt

copy c:\check\first.txt +
c:\check\middle.txt +
c:\check\end.txt c:\inetpub\wwwroot\monitor.htm

REM Note – the above three lines need to be on one line – wraps
REM because of the word processor used.The batch file can be run by schedule or on demand. Now the user at the remote site can enter http://servername/monitor.htm and they can see the status of the SQL Server. 

As you can see, the OSQL tool has uses beyond just selecting some data to the screen. Hopefully you can think of other uses for the tool to solve problems you’ve had with a "headless" system.

Informit Articles and Sample Chapters

I've got some more practical examples on using these tools here.

Online Resources

In this article Microsoft shows you more about using these tools to monitor your server.