Click here to Skip to main content
11,490,566 members (37,588 online)
Click here to Skip to main content

SQL SERVER - 2005 - Introduction and Explanation to sqlcmd

, 6 Sep 2007 CPOL 30.2K 23
Rate this:
Please Sign up or sign in to vote.
SQL SERVER - 2005 - Introduction and Explanation to sqlcmd

Introduction

I decided to write this article to respond to a request of one usergroup, which requested that they would like to learn sqlcmd 101.

SQL Server 2005 has introduced a new utility sqlcmd to run ad hoc Transact-SQL statements and scripts from command prompt. T-SQL commands are entered in command prompt window and the result is displayed in the same window, unless the result set is sent to the output files. sqlcmd can execute single T-SQL statement as well as batch file. sqlcmd utility can connect to earlier versions of SQL Server as well. The sqlcmd utility uses the OLE DB provider to execute T-SQL commands, whereas SQL ServerManagement Studio uses .NET sqlclient to execute sqlcmd script, this can lead to different results in certain cases. (If you have an example of this, please let me know. I will post it here.)

sqlcmd is an enhanced version of the isql and osql and it provides way more functionality than the other two options. In other words, sqlcmd is a better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work two modes:

  1. BATCH and
  2. interactive

Let us go over a simple example of sqlcmd.

Step 1: Go to Start >> Run >> Type "cmd" and press enter:

Step 2: Type in command "sqlcmd" and press enter:

Step 3: Type the following "USE AdventureWorks" command to switch database context to Adventureworks. Type "GO" after the batch to change the code. It will display the success message as "Changed database context to AdventureWorks".

Step 4: Now run any same query. Refer to the following image to see the query and its result.

Step 5: We will get a similar result if the same query is run in Query Editor in SSMS.

Step 6: Type "exit" at any point if you do not want to continue working with sqlcmd.

The use of sqlcmd syntax is very easy, however, this command can perform many powerful tasks. We will see that in future articles.

Reference: Pinal Dave (http://www.SQLAuthority.com), BOL.

History

  • 6th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
GeneralFinally... Pin
joebeam6-Sep-07 11:44
memberjoebeam6-Sep-07 11:44 
GeneralRe: Finally... Pin
pinaldave6-Sep-07 12:15
memberpinaldave6-Sep-07 12:15 
GeneralRe: Finally... [modified] Pin
joebeam7-Sep-07 6:55
memberjoebeam7-Sep-07 6:55 
GeneralRe: Finally... Pin
vjedlicka6-Sep-07 22:52
membervjedlicka6-Sep-07 22:52 
GeneralRe: Finally... Pin
joebeam7-Sep-07 7:08
memberjoebeam7-Sep-07 7:08 
GeneralRe: Finally... Pin
pinaldave7-Sep-07 7:42
memberpinaldave7-Sep-07 7:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 6 Sep 2007
Article Copyright 2007 by pinaldave
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid