SQLCMD VS OSQL

In SQL Server 2005, Microsoft introduced a new command line utility, SQLCMD, as a replacement for osql and isql.
In this article you will find some advantage you will gain when you use SQLCMD instead of OSQL.
Migration from OSQL to SQLCMD will be very simple because most of the command line switches are similar,the only difference is that SQLCMD support additional switches.To see the difference between then type OSQL /? and SQLCMD /? at the command prompt.

1) SQLCMD supports parameterized variables
SQLCMD can be useful for running some SQL scripts which require you to pass different parameters specific to a certain environment.Environment variables and declared variables can be used in various places in the script The following is an example:
-- c:\sqlvariable.sql
DECLARE @myVar varchar(255)
SET @myVar = '$(myVar)'
SET @myVar = @myVariable + ' ' + suser_sname()
PRINT @myVar

You can run it from a command prompt:
SQLCMD -E -i"c:\sqlvariable.sql" -v myVar="You are connected as "
Result:
You are connected as ABOUHMEID02\Administrator

2) !!
This command allows you to execute an Operating System Command on the client without having to connect and use xp_cmdshell. For example while connected to SQLCMD type !!dir

3) Management Studio's query editor in the SQLCMD mode
SQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like color coding, parsing and executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements. You can set this in Tools Options or click the button with the Red

4) Performance
It allows you to connect to any instance of SQL Server via OLE DB (rather than via older technologies such as DB-library and ODBC, used by OSQL and ISQL, respectively) which is a much better performer

5) Remote Dedicated Admin Connection (DAC)
If you use the -A switch, you can get into a server that might otherwise be hung. You can kill an offending process and restore order to the universe - without a reboot!

6) SQLCMD support Customized editor
In SQLCMD, if you type ed, it will invoke a text editor and put the last command you run in the editor buffer. The default editor is Edit.com You can change the default editor to your own preferred editor,to do this: Open a DOS prompt; Type set sqlcmdeditor=notepad Go to sqlcmd, enter and execute a sql statement, then type ed; The notepad opens.Do the modifcations you want to do, save and close.

7) SQLCMDINI
This allows you to set default parameters and even create a default query to execute on the server as soon as you connect. This helps you to automate your connection properties and to some degree your environment in command line mode. For example: Create a script file named :
-- c:\Init.SQL
-- Begin script
set nocount on go
print 'You are connected to ' + rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) + ' (' + rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) + ')' + ' '
+ rtrim(CONVERT(char(30), SERVERPROPERTY('Edition'))) + ' '
+ rtrim(CONVERT(char(20), SERVERPROPERTY('ProductLevel')))
+ char(10)
set nocount off go
--End script
In Command prompt, type: set sqlcmdini=c:\Init.SQL Here are some sample results: C:\DOCUME~1\ADMINI~1>sqlcmd You are connected to ABOUHMEID02 (8.00.760) Developer Edition SP3 1>

8) :XML [ONOFF]
Specifies if XML output resulting from FOR XML clause will be output as a continuous stream.

9) :Connect server_name[ \instance_name][timeout] [ user_name[ password]]
From within a single script, you can connect to multiple SQL Server instances and execute commands. This allows for a great deal of automation possibilities, such as backup from one server, restore to another server. Also, you could execute a configuration change script on all servers in production.

10) :Error STDERRSTDOUT
There's pretty much all you need in some combination of these to gracefully deal with run-time problems. That is so much better than piping out and digging through log files! It Redirects all error output to the file specified by filename, to stderr or to stdout. The Error option can appear multiple times in a script. Error output is sent to stderr by default.

11) :Perftrace STDERRSTDOUT
Redirect all performance trace information to the file specified by filename, to stderr or to stdout. Performance trace output is sent to stdout by default. It lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.