SQL Server

Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/3/2011 9:16:41 AM

3. Sqlcmd

If you've worked with SQL Server for any length of time, chances are you've worked with the sqlcmd utility. This utility lets you enter and execute T-SQL statements and other objects via a command prompt. You can also use the sqlcmd utility via the Query Editor in sqlcmd mode, in a Windows script file, or via a SQL Server Agent job.

This section discusses how to use the sqlcmd utility to connect to a SQL Azure database and execute queries against that database. This section assumes that you have some familiarity with sqlcmd. This utility has many options, or parameters, but this section only discusses those necessary to connect to SQL Azure.


SQL Azure doesn't support the -z or -Z option for changing user passwords. You need to use ALTER LOGIN after connecting to the master database in order to change a password.

To use the sqlcmd utility, you first open a command prompt. At the command prompt, you need to provide the options and values necessary to connect to the SQL Azure database. As a minimum, the command syntax is the following:

sqlcmd -U login -P password -S server -d database

The parameters are nearly self-explanatory, but here they are, just in case:

  • -U is the user login ID.

  • -P is the user-specified password. Passwords are case sensitive.

  • -S specifies the instance of SQL Server to which to connect.

Optionally, you can provide a database name via the -d parameter. Thus, the sqlcmd syntax looks something like the following:

Sqlcmd -U providerlogin@Server -P ProviderPassword -S ProviderServer -d database

Let's put this syntax to use. Follow these steps:
  1. At the command prompt, use the sqlcmd syntax and type in your connection information, as shown in Figure 3. (In the figure, the server name and password are hidden.) Press Enter.

    Figure 3. Connecting via sqlcmd
  2. When the sqlcmd utility connects, you're presented with the sqlcmd prompt 1>, at which point you can begin typing in and executing T-SQL commands. The command to execute any T-SQL statement is GO. For example, in Figure 4, the following SELECT statement is entered and executed:

    SELECT Name FROM Users

  3. Press the Enter key on line 1> to create a new line. Pressing Enter executes the SELECT query. Type GO on line 2> and press Enter, to execute all statements since the last GO statement (see Figure 4). Figure 5 shows the results of the sqlcmd query entered. As you can see, executing a query isn't difficult.

    Figure 4. Executing a SELECT
    Figure 5. Sqlcmd query results

Let's work through another example in which you create a table and add data. Here are the steps:

  1. After the previous query is finished, you're back at the 1> prompt. Type in the statement shown in Figure 6.

    Figure 6. Creating a table
  2. Press Enter, type GO on line 2>, and press Enter again, to execute the CREATE statement.

  3. When the T-SQL command that you execute is the type that doesn't return data, the sqlcmd utility doesn't give you back a message but takes you to the 1>Figure 7 shows the results from doing that—you can see that the table was indeed created. prompt. However, you can verify that a statement executed successfully by going into SQL Server Management Studio (SSMS), connecting to your SQL Azure instance, and expanding the Tables node of your chosen database.

The table you created is called TechGeoInfo, and it has three columns: an ID column that is the primary key (clustered index), a TechID column, and an address column. The table is simple, but it's good enough to demonstrate functionality.

Figure 7. Table in SSMS

  1. Add some data to the table by going back to the command window and typing in the INSERT statements shown in Figure 8. The great thing about the sqlcmd utility is that you can enter in as many commands as you want and not execute them until you type GO. Here you use two INSERT statements that add two records the table you created in the previous step.

  2. Type GO on line 3>, and press Enter. Although the sqlcmd utility tells you 1 rows affected, you can query this new table in SSMS and see the two new rows that were added, as shown in Figure 9.

    Figure 8. Inserting rows via sqlcmd
    Figure 9. Viewing results via SSMS

As you can see, using the sqlcmd utility is straightforward. Just remember that it doesn't work with SQL Azure if you're trying to use heap tables. All tables must have a primary key. Also, as mentioned earlier, the -z and -Z parameters don't work.

This section has discussed the different mechanisms for connecting and querying SQL Azure, including examples for ADO.NET, ODBC, and SqlCmd. You can see that it quite similar to the way you currently connect to and query an on-premise database. However, with an overall industry push to an SOA architecture, let's take the discussion to the next level and discuss using services, specifically WCF Data Services, to connect to our Azure database.

Other -----------------
- Programming with SQL Azure : Application Deployment Factors
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 3)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 2)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 1)
- SQL Server 2008: SQL Server Web Services
- SQL Server 2008: SQL Server Service Broker - Related System Catalogs
- SQL Azure Backup Strategies (part 2)
- SQL Azure Backup Strategies (part 1) - Copying a Database
- SQL Server 2008: Troubleshooting SSB Applications with ssbdiagnose.exe
- SQL Server 2008: Service Broker Routing and Security
- Migrating Databases and Data to SQL Azure (part 9)
- Migrating Databases and Data to SQL Azure (part 8)
- Understanding Service Broker Constructs (part 5)
- Understanding Service Broker Constructs (part 4) - Creating the Conversation Initiator
- Migrating Databases and Data to SQL Azure (part 7)
- Migrating Databases and Data to SQL Azure (part 6) - Building a Migration Package
- Migrating Databases and Data to SQL Azure (part 5) - Creating an Integration Services Project
- Understanding Service Broker Constructs (part 3)
- Understanding Service Broker Constructs (part 2) - Creating Queues for Message Storage
- Understanding Service Broker Constructs (part 1) - Defining Messages and Choosing a Message Type
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Video Sports
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
Popular tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS