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.
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.
There are many more advantage of using sqlcmd which we will see in follow up posts.
Reference : Pinal Dave (https://blog.sqlauthority.com)
when i run “OSQL -L” is supposed to return all the server names that are running SQL Server, but it is returning more than it is supposed to. Why is that?
I have a SQL Server field of type varchar(max). I have an automated batch process that uses sqlcmd to pull data from a database and dump it into a text file. We have fields with more than 256 characters in them, and these fields get cut off unless I add something like “-y 0” to the flags in the sqlcmd call.
This gives me the full text for fields larger than 256 characters, but it also adds a great deal of whitespace–the fields are padded to make each field as big as it could possibly be according to its data type, essentially given me huge files with lots of padding and waste space.
I could fix this by adding -W to my sqlcmd flags, but this gives me an error saying that -W and -y are incompatible.
Has anyone had this problem before? Thoughts on how to solve it?
I dont’t have the same problem, but I find this comment useful, I was searching for the “-y 0” option.
Is it possible to also list the ports on which SQL Servers are running in network along with the Server Instance Name while we run the command sqlcmd -L ?
You need to connect to each instance and use WMI or read registry key via PowerShell.