Programming4us
         
 
 
SQL Server

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

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.

NOTE

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 : Connecting to SQL Azure (part 3) - ODBC
- Programming with SQL Azure : Connecting to SQL Azure (part 2)
- Programming with SQL Azure : Connecting to SQL Azure (part 1) - ADO.NET
- 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
 
 
Most View
- Microsoft XNA Game Studio 3.0 : Adding Bread to Your Game (part 1) - Using a Structure to Hold Sprite Information, Using the Gamepad Thumbsticks to Control Movement
- Windows 8 : Applications - Program Shortcuts and Compatibility
- Windows 7: Managing Wireless Network Connections (part 3) - Reordering Wireless Connections
- Windows Server 2008 Server Core : Getting System Configuration Information with the SystemInfo Utility
- Exchange Server 2010 : Manage Database Redundancy (part 3) - Manage Database Availability
- SharePoint 2010 : Recover a Deleted File or List Item
- Windows 7 : Creating a Windows Network - Planning Your Network
- Exchange Server 2010 Mailbox Services Configuration (part 4) - Client Configuration
- Overview of Internet Explorer 8 (part 1) - Defining IE8 Accelerators
- Exchange Server 2010 : Upgrading from and Coexisting with Exchange Server 2003 (part 7) - Coexistence for Management
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS