Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Using the Sqlcmd Utility

By Exforsys | on December 18, 2005 |
SQL Server 2005

SQL Server 2005 – Using the Sqlcmd Utility

In this tutorial you will learn about SQL Server 2005 – Using the Sqlcmd Utility, Running the sqlcmd utility, Sqlcmd rules – Quoted Strings, Code Page Conversion Rules and Interactive sqlcmd Example.

The interactive execution of T-SQL statements and scripts is enabled by the Win32 Command prompt utility called sqlcmd utility. This utility can be used to build scripts to be run by sqlcmd users or to interactively enter T-SQL statements into the utility in a manner that is similar to working with the command prompt window. Users can submit a sqlcmd job as a single T-SQL statement for execution or point the utility to a text file that contains the required T-SQL statements. The output is then displayed in the window or in a text file.

Running the sqlcmd utility

Open the command prompt window and type sqlcmd at the prompt. A number of switches can be used ot specify the options of the utility.

The –s switch is a server switch that identifies the instance of the SQL Server to which the utility is to be connected. –E,-U and –P specify the credentials sqlcmd uses to connect (the –E is default). Input switches are –Q, -q and –i and output switches are –o.

When the sqlcmd utility is executed without input files or queries the sqlcmd connects to the default instance of MSSQL Server and displays the 1> called the sqlcmd prompt. The1 signifies the first line of the transact sql statement to be entered. In other words in the sqlcmd prompt the user can type sqlcmd commands and T-SQL statements such as GO and EXIT. Each T-SQL statement is buffered in a statement cache and are sent to the SQL server after the GO statement followed by the Enter key. The sqlcmd can be terminated using the EXIT command. The statement cache can be cleared by typing RESET. ^C can be used to stop the execution of a statement cache after the GO command is given.

Sqlcmd rules:

Quoted Strings

When Characters are enclosed in quotes, no preprocessing is done. SQL Server treats quotes inserted into a string as consecutive quotes as a single quote. Moreover, scripting variables are not expanded when they appear within a string. When strings span multiple lines they still treated as a single string.

Code Page Conversion Rules

If code pages are specified sqlcmd uses the code page both for input and output files. If the input file is a Unicode file, no conversion is attempted. The big-endian and little-endian Unicode input files are automatically recognized. If the –u option is used the output will default to little-endian. When no output file is specified the output page displayed will be the console page. If multiple input files are specified, they are all assumed to be the same code page. Unicode and non Unicode files can be mixed.

Interactive sqlcmd Example

1. Open the command prompt window
2. Type sqlcmd at the command prompt.
3. Type USE EXFORSYS and press enter and then enter GO and press enter.
4. You will get a display “Changed database context to ‘Exforsys’
5. After the output is generated, sqlcmd once more displays the 1> prompt on which the user can type EXIT and exit the utility. You can again type EXIT to exit the command prompt.

Database script files can also be executed using the sqlcmd utility. Database script files contain a mix of T-SQL statements and sqlcmd commands and scripting variables. Sqlcmd runs through the input file without a pause and does not wait for any user inputs.

Database script files can be built in different ways. The first of these methods is to write T_SQL statements in the Microsoft SQL Server Management Studio and save the contents to the Query window as a script file. The second method is to create a text file containing a T-SQL statement using a text editor.

To run a script file using sqlcmd open Notepad and type T_SQL statements as under:

USE Exforsys
GO
SELECT TOP(5) STUDENTID, FName, LName
FROM Student.Contact
GO

Save the file as ExforsysScript.sql in a folder under the C:\. Now run the following command from the command prompt and place the output file in the same folder as Exforsysoutput.txt. Now view the contents of the file. You will see the data in the following format:(The data below is an illustration and not derived from an actual database)

Changed database context to ‘Exforsys’

StudentID ……..FName ……..LName
……..1 …………….James ………Collen
……..2 …………….Abba ………….Raymond

A number of variables can be used in scripts and are known as scripting variables. These variables provide the user with flexibility of using a single script in multiple scenarios. These scripting variables can be defined explicitly using the setvar command or implicitly using sqlcmd switches. All variables defined using setvar command take precedence over variables defined as environment variables.

If sqlcmd is started with a switch that has a related sqlcmd variable, the variable is set implicitly to the value specified using the switch. For instance if sqlcmd is started with the -l switch the variables are implicitly set to SQLLOGINTIMEOUT variable.

While naming the scripting variables certain rules and conventions are followed. The variable must not contain white space characters and they must not have the same form as a variable expression. Also, variable names must be quoted if the string values contain spaces. If the variable names contain quotation marks they must be escaped. It must be remembered that scripting variable names are case sensitive and when no value is assigned to a sqlcmd environment variable the same is removed.

« « Using the SQL Management Objects
SQL Server 2005 Configuration Manager » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 391 Followers
  • Popular
  • Recent
  • SQL Server 2005 Installation – Maintenance Plan Without Using Wizard

    December 2, 2005 - 0 Comment
  • Security Features in SQL Server 2005 for the Developer

    October 15, 2005 - 0 Comment
  • SQL Server 2005 – Populating the Database

    December 18, 2005 - 0 Comment
  • SQL Server 2005 Administrative Tools

    November 19, 2005 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Unattended Installations

    December 7, 2005 - 0 Comment
  • T-SQL Enhancements in SQL Server 2005

    October 15, 2005 - 0 Comment
  • SQL Server 2005 – Database Backup

    December 20, 2005 - 0 Comment
  • SQL Server 2005 – Registered Servers

    November 19, 2005 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT