SQLCMD, pronounced “SQL command,” is a command-line tool used to connect to SQL Server and submit T-SQL queries and commands. With SQLCMD, you can perform the following:
SQLCMD was designed as a replacement to the existing OSQL and ISQL utilities. Of these two older utilities, only OSQL stills ships with SQL Server 2008; however, it has been deprecated since SQL Server 2005. Switching to SQLCMD from these older tools is definitely worth your time. The SQLCMD code was written from scratch, and a lot of effort was put into performance and features that promote usability, such as the ability to pass variables. SQLCMD also supports datatypes like nvarchar(max) and XML.
SQLCMD is designed so that the user can simply swap calls to OSQL.EXE with SQLCMD.EXE and have the script work without any modifications.
Connecting to SQL Server
Unlike OSQL and ISQL, which used ODBC to connect to SQL Server, SQLCMD uses an OLE DB connection and allows users to make multiple connections to different servers within the same script. For example, suppose you had a few simple backup database scripts that each backed up a database on a specific server. On SERVERONE, the administrator would run this backup script to back up the ReportServer database:File: backup_ReportServer.sql
On SERVERTWO, the administrator would run this backup script to back up the Products database:File: backup_Products.sql
In the real world, we know that administrators tend to have a lot of scripts that each performs its own functions on a specific server. With SQLCMD, you can now consolidate these into a single script using the :CONNECT command. Let’s see this same scenario of backing up multiple databases using a single script:File: backup_databases.sql
Issuing the SQLCMD command sqlcmd -E -i backup_databases.sql yields the following result:
SQLCMD also provides the ability to pass variables from the command line and within the script itself. For example, assume you have a generic “backup database” script, called backup_database_generic.sql, that could be reused:File: backup_database_generic.sql
At this point, you could call this script from the command line using the new -v parameter. This parameter tells SQLCMD that the following text is a variable, an example of which is shown here:C:>SQLCMD –E –i backup_database_generic.sql
When the backup_database_generic.sql script is run, it will have two variables defined: myConnection, which is equal to ".", and myDatabase, which is equal to "ReportServer". Alternatively, if you wanted to use variables, you also could have set the parameters within another script:File: backup_database_main.sql
When this script is executed, SQLCMD will set the myConnection variable to "." (the period is an alias for the local server—you could have used "localhost" or the actual name of the server as well), set the myDatabase variable to "ReportServer", and then insert the contents of the backup_database_generic.sql script in-line.
Using the Dedicated Administrator Connection
SQL Server spawns a separate thread called the dedicated administrator connection (DAC). This connection was designed to be used by members of the sysadmin role in the event that they can’t connect to SQL Server under normal operating conditions. There is only one connection of this type allowed per instance of SQL Server available, and it can be accessed only through SQLCMD. To connect to SQL Server 2005 on the DAC, use the -A parameter in SQLCMD.
C:>SQLCMD –E –S. -A
Although some might claim Notepad is the best text editor around, writing scripts for SQLCMD can sometimes be tedious using even this robust text editor application. For this reason, SQL Server Management Studio supports writing SQLCMD scripts.
To create a new SQL Script, open Management Studio, connect to your server, and click the New Query button. On the context menu, select New SQL Server Query. You’ll now have a Query Editor window open. If you start typing SQLCMD commands in this window, you’ll notice that the editor will complain, and you’ll get errors if you try to execute the script.
SQL Server Management Studio not in SQLCMD mode
These errors occur because you first need to enable SQLCMD mode in the editor. You can enable this mode by selecting SQLCMD mode from the Query menu in Management Studio. When the editor is in SQLCMD mode, you’ll notice that SQLCMD-specific commands such as :SETVAR and :CONNECT are highlighted in the editor. This is to differentiate them from traditional T-SQL.
SQL Server Management Studio in SQLCMD mode
Once the SQLCMD mode is enabled on the editor, the script can be executed by clicking the Execute button. This will pass the script to the SQLCMD application to be interpreted instead of submitting it to the SQL Server database engine directly.
SQLCMD is a complete rewrite of the deprecated OSQL command-prompt utility. It is designed with many performance-related improvements.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.