Lesson 1: Starting sqlcmd
To begin using sqlcmd, you must first launch the utility and connect to an instance of SQL Server. You can connect
to either a default or named instance. The first step is to start the sqlcmd utility.
Note:
Windows Authentication is the default authentication mode for sqlcmd. To use SQL Server Authentication, you
must specify a user name and password by using the -U and -P options.
Note:
By default, SQL Server 2005 Express Edition installs as the named instance sqlexpress.
If you have not connected to this instance of the SQL Server Database Engine before, you may have to configure
SQL Server to accept connections. For more information, see the tutorial Getting Started with the Database Engine.
To start the sqlcmd utility and connect to a default instance of SQL Server
1. On the Start menu click Run. In the Open box type cmd, and then click OK to open a Command Prompt
window.
2. At the command prompt, type sqlcmd.
3. Press ENTER.
You now have a trusted connection to the default instance of SQL Server that is running on your
computer.
1> is the sqlcmd prompt that specifies the line number. Each time you press ENTER, the number
increases by one.
4. To end the sqlcmd session, type EXIT at the sqlcmd prompt.
To start the sqlcmd utility and connect to a named instance of SQL Server
1. Open a Command Prompt window, and type sqlcmd -S myServer\instanceName. Replace
myServer\instanceName with the name of the computer and the instance of SQL Server that you want to
connect to.
2. Press ENTER.
The sqlcmd prompt (1>) indicates that you are connected to the specified instance of SQL Server.
Lesson 2: Running Transact-SQL Script Files by Using sqlcmd
After you use sqlcmd to connect to a named instance of Microsoft SQL Server, the next step is to create a
Transact-SQL script file. A Transact-SQL script file is a text file that can contain a combination of Transact-SQL
statements, sqlcmd commands, and scripting variables.
To create a simple Transact-SQL script file by using Notepad, follow these steps:
1. Click Start, point to All Programs, point to Accessories, and then click Notepad.
2. Copy and paste the following Transact-SQL code into Notepad:
Copy Code
USE AdventureWorks
GO
SELECT [Link] + ' ' + [Link] AS 'Employee Name',
a.AddressLine1, a.AddressLine2 , [Link], [Link]
FROM [Link] AS c
INNER JOIN [Link] AS e
ON [Link] = [Link]
INNER JOIN [Link] ea ON
[Link] = [Link]
INNER JOIN [Link] AS a ON [Link] = [Link]
GO
3. Save the file as [Link] in the C drive.
To run the script file
1. Open a command prompt window.
2. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\[Link]
3. Press ENTER.
A list of Adventure Works employee names and addresses is written to the command prompt window.
To save this output to a text file
1. Open a command prompt window.
2. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\[Link] -o
C:\[Link]
3. Press ENTER.
No output is returned in the Command Prompt window. Instead, the output is sent to the [Link] file. You can
verify this output by opening the [Link] file.
