SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

A day before I wrote article SQL SERVER – sqlcmd vs osql – Basic Comparison. Today while I was displaying how sqlcmd can be used instead of osql to one of my companies team leader, I found another neat feature of SSMS Query Editor. sqlcmd can be used from Query Editor but it has to be enabled first.

Following image display how sqlcmd can be enabled in Query Editor. Go to SSMS Menu >> Query >> (click on ) SQLCMD Mode.

Now on Query Editor will support sqlcmd mode. Let us see following example where script contains operating system commands as well SQL commands.

SELECT @@Version ServerVersion
!! Dir "C:\Documents and Settings"
!! Date /T

Once above command has ran following output is displayed in Result Window.

Interesting Observation:
What really is interesting is that out of complete batch all the operating system commands runs first and those are followed by SQL commands. The order of SQL and operating system does not matter in resultset.

I want to throw my above observation to all of you and want to get your feedback about that. Have you faced this situation or scenario? What is the solution some we want to execute SQL and OS commands in order?

Watch the video on the same subject:

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

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.



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 (http://blog.SQLAuthority.com)

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)

SQL SERVER – Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server.

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “<LinkedServerName>”. The provider supports the interface, but returns a failure code when it is used.


Use Windows Authentication mode
For a default instance
osql -E -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance
osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

Use SQL Server Authentication mode
For a default instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

By default, this folder is C:\Program Files\Microsoft SQL Server\MSSQL\Install.

osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

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