SQL SERVER – sqlcmd vs osql – Basic Comparison

Today we will go over very simple but to the point comparison of two SQL Server utilities or SQL Server tools.

This comes often to users which one to use sqlcmd or osql, when in need of running SQL Server queries from command prompt. Answer to this is very simple use “sqlcmd”.

sqlcmd has all the feature which osql has to offer, additionally sqlcmd has many added feature than osql. isql was introduced in earlier versions of SQL Server. osql was introduced in SQL Server 2000 version. sqlcmd is newly added in SQL Server 2005 and offers additionally functionality which SQL Server 2005 offers.

osql

SQL SERVER - sqlcmd vs osql - Basic Comparison osql

sqlcmd

SQL SERVER - sqlcmd vs osql - Basic Comparison sqlcmd

It is worth noting that when osql command is run on system where SQL Server 2005 is installed it gives following message in the beginning, which itself suggests to use sqlcmd.

Note: osql does not support all features of SQL Server 2005.
Use sqlcmd instead. See SQL Server Books Online for details.

If there are scenario where the script is using very basic functions of SQL Server and either sqlcmd and osql can execute them what user should do? In this case, I would suggest that use sqlcmd because sqlcmd is feature rich and why not use something which is latest.

Let me give one example, in one of the recent script which I came across required to run OS commands along with SQL commands. If osql was only used then whole script has to divide in two parts. osql would run SQL commands and XP_CMDSHELL would run OS commands. However, using sqlcmd both can be achieved easily.

If you are using sqlcmd and you need to run system command “dir” just write “!! dir” and it will run system command right away.

SQL SERVER - sqlcmd vs osql - Basic Comparison sqlcmd1

There are many more advantage of using sqlcmd which we will see in follow up posts.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2008 – Change Color of Status Bar of SSMS Query Editor
Next Post
SQL SERVER – Find Currently Running Query – T-SQL

Related Posts

26 Comments. Leave new

  • One can set variable in SQLCMD mode too, if running T-SQL script in SQLCMD mode

    :SETVAR ServerName “Server”
    :SETVAR ManagementDatabase “MDW”
    GO
    :CONNECT $(ServerName)
    GO

    ………….usual T-SQL code………….

    Reply
  • Hi! I not sure if I understood this article. I made the !!dir example with osql and every happens in the same way than sqlcmd.

    Then… what’s the difference??

    Thanks

    Reply
  • If your targets are a mixed envirionment of 2005 and 2000, will sqlcmd work on the 2000 environment servers? This is an important function that you do not mention.

    Reply
  • @steve,

    SQLCMD does not work with SQL Server 2000.

    OSQL works with SQL Server 2000.

    Kind Regards,
    Pinal

    Reply
  • I have SQL Server 2005 Management Studio on one of my PCs, but on a client’s computer who will not allow me to install SQl Server on it or any app that requires Admin priviledges, I need to submit TSQL commands to alter SQL 2005 Database tables remotely. Will SQLCMD or another utility allow me to do that?

    Reply
  • Edward Manning
    March 3, 2010 6:08 pm

    Thanks Pinal,

    Was trying to write a bat file to give to the DBAs to execute on our production boxes and the information given here was exactly what I needed.

    Cheers
    Ed

    Reply
  • please elaborate the topic to cover much detail……..

    Reply
  • Pinal,

    Can we use sqlcmd on a system where SQL Server is not installed. I have to execute script file from my machine to my database server.

    –Faisal Iqbal

    Reply
  • Hello Pinal,
    I Have a large file with calls to a Stored Procedure(about 3.5GB). sqlcmd sometimes says scripting error I think its a timeout error but not sure, in the other hand osql execute it without any problem, but its taking too much time to get done.

    Is there a way or good practices to do it faster?

    Reply
  • I am using a batch file written by someone else. It uses osql with a -n parameter, but I don’t see a comparable parameter in sqlcmd. Do I need this? If so, what does it do for me?

    The help text says “Removes numbering and the prompt symbol (>) from input lines.” Can you explain what this actually means?

    Thanks… mpv

    Reply
  • HI !

    After I installed MS SQL server management studio express (version 2005) I can’t execute sqlcmd.exe in command prompt.

    I should be able to access sqlc,d.exe in folder C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/Binn/ and then add it to PATH variable, but the file sqlcmd.exe is not located there.

    Moreover I installed MS SQL server mgmt studio express on my desktop and I want to connect from my desktop.

    Could you please help me on that? Is it the operating system that is not supported I use MS Windows XP Home edition with Service Pack 2 ?

    Thanks in advance for your help

    Purnima

    Reply
  • There is a free tool “SQLS*Plus” which is an SQL*Plus for SQL Server. Works with SQL Server 2000/2005 and 2008

    Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), etc – light years better than isql, osql
    or sqlcmd

    I found it at

    Reply
  • can we schedule a job from commad prompt while usong osql command.

    please tell me if any idea.

    Regards,
    BASAVA

    Reply
  • how to aumate database(test) sql server 2008 backup via command prompt??

    Reply
  • how to automate test-database (sql server 2008) backup via command prompt?

    Reply
  • Hi how do I send an output of sql from sqlcmd as an email?Please help

    Reply
  • Kapil Chauhan
    April 1, 2012 8:58 pm

    Hi, I have created a stored procedure in sql server 2005 to send email using send_db_mail function of sql procedure. I am calling this procedure in a batch file using sqlcmd of sql server 2005. When i run or schedule that batch file, it’s run perfectly and send email to reception but the pop up email queued does not close automatically. can someone help me to close that pop up automatically using any command in batch file. Thanks in advance.

    Reply
  • Is there a way to do something similar to the following:

    –=========================
    declare @servername varchar(50)

    select top 1 @servername = servername from myServers

    :setvar ServerName @servername
    :CONNECT $(ServerName)
    –========================

    Obviously the above doesn’t work. I want to connect to another server without explicitly giving the server name.

    Thanks

    Reply
  • SQLCMD is just too slow for large data files. Just too slow. Osql blazes. What gives?

    Reply
  • I’ve recently changed over from running a set of scripts using osql to sqlcmd. This has increased the time taken to run the scripts significantly: With osql it took around 30 minutes and with sqlcmd around 2 hours. Any idea why? There were no other significant changes on the server.

    Reply

Leave a Reply