SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

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

SQL Server 2005 has introduced 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 result is displayed in the same window, unless result set are sent to 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 example of this please let me know, I will post it here)

sqlcmd is enhenced version of the isql and osql and it provides way more functionality than other two options. In other words sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work two modes – i) BATCH and ii) interactive modes.

Let us go over simple example of sqlcmd.

1) Step 1 : Go to Start >> Run >> Type “cmd” and press enter.

2) Step 2 : Type in command “sqlcmd” and press enter

3) Step 3 : Type 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”.

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

5) Step 5 : Similar result we will get if the same query is ran in Query Editor in SSMS.

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 it this command can perform many powerful tasks. We will see that in future articles.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

11 thoughts on “SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

  1. i just want to know when you have 2 intances running then how can we connect to the right instance in sqlcmd.
    I got two instance one is local and named but i want to use named…any suggestion.

    Like

  2. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  4. Hi pinal,

    Could you please guide can i able to delete a file from network system through SQL Server ?

    Thanks & Reagards,

    Karan

    Like

  5. Hi Pinal,

    I have a requirement where on a monthly basis, I have to export data to Access mdb. On our main SQL server I run a data collection for the company, but then in Access import the data to several .mdbs (one for each contract).

    At present I use the SQLCMD in access to do the splits. What I am looking at is to run the splits as part of the data collection job i.e. Automate it from SQL rather then having to do it manuall after the collection job has completed. I have been searching high and low to find a solution and came across your article on “Exporting Data from SQL to Access in Mdb file”
    in you you describe a Backup proc you created. How can I modify this to use for my application?

    Don’t forget, I have a several tables (around 11 in all with specific names holding the data for the whole company) I need to get the data for each contract and export it to the relevant .mdb (with the same named tables) and then move to the next contract.

    Any help guidence you give will be most welcome.

    Thanks,

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 Journey to SQLAuthority

  7. i just want to know when you have 2 intances running then how can we connect to the right instance in sqlcmd.
    I got two instance one is local and named but i want to use named

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s