Introduction to sqlcmd utility in SQL Server 2005
IntroductionI 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
sqlcmdto 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.
sqlcmdcan execute single T-SQL statement as well as batch file.
sqlcmdutility can connect to earlier versions of SQL Server as well. The
sqlcmdutility uses the OLE DB provider to execute T-SQL commands, whereas SQL ServerManagement Studio uses .NET
sqlcmdscript, 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.)
sqlcmdis an enhanced version of the
osqland it provides way more functionality than the other two options. In other words,
sqlcmdis a better replacement of
isql(which will be deprecated eventually) and
osql(not included in SQL Server 2005 RTM).
sqlcmdcan work two modes:
- BATCH and
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
The use of
sqlcmdsyntax is very easy, however, this command can perform many powerful tasks. We will see that in future articles.