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
 

Using SQL Sever Management Studio – Part 1

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

Using SQL Sever Management Studio – Part 1

In the tutorial you will learn about Using SQL Sever Management Studio, The features of the Management Studio, Starting SQL Server Management Studio, The Object Explorer and also Default. Optional connection settings.

A Database Administrator typically grapples with tasks relating to accessing, configuring and administrating the components of the SQL Server. To assist the Administrator in this task, SQL Server 2005 provides a graphical user interface called the SQL Server Management Studio. The SQL Server Management Studio combines graphical user interface with a rich script editor, so that administrators can script and administrate from a single window.

The SQL Server Management studio harnesses the power of the Enterprise manager and Query Analyzer of the earlier versions and also provides a single window to the other functionalities such as Analysis Services, Integration Services, Reporting Services and XQuery. The features of the Management Studio can be listed as under:

1. Supports administrative tasks for SQL Server 2005 and 2000.
2. Provides an integrated environment for Database Engine management and authoring
3. Provides access to SQL Server database engine, Analysis Services, Reporting Services, Notification Services, SQL Server Mobile and the script editor.
4. The dialog boxes are non modal and hence user is able to access multiple tools simultaneously.
5. The scheduling dialog box allows users schedule management tasks as per convenience.
6. Server registrations can be exported or imported from one Management Studio to another.
7. XML Showplan or Deadlock files generated by the SQL Server Profiler can be saved or printed for review.
8. The error informational message box facilitates user comments on error messages. Error messages can be copied to clipboard and emailed to the support team at Microsoft or within the organization.
9. The integrated web browser enables ease of access to MSDN and online help.
10. The Help system is also integrated with MyHelp and online communities.
11. Server tutorials help users hit the ground running when deploying the server for the first time!
12. The management studio contains a activity monitor and an automatic refresh which is useful.
13. It also has integrated database mail interfaces.
14. The code editor component of SQL Server Management Studio contains several integrated script editors that help in authoring T-SQL, MDX, DMX, XML/A and XML scripts. The rich set of templates also can be supplemented with custom templates. Queries can be written without a connection to the server and XML results can be viewed. Versions of scripts can be maintained and the whole is supported by IntelliSense.

Starting SQL Server Management Studio

When SQL Server 2005 is installed, Programs are added to the Start Menu. Click on Start > All Programs > Microsoft SQL Server CTP > SQL Server Management studio.

The first task that the user needs to undertake is connecting to the server. The Connect to Server dialog box appears and the users are prompted to select the server and the authentication mode. If windows authentication has been selected then, click connect to start the server, else select the authentication mode and the server and click connect to connect to the server.

As soon as the server connection is established the user is within the SQL Server Management Studio interface. Note that the window contains several parts. The tool bar and Menu Bar which give access to various options; the Registered servers window which gives details of registered servers on the system; an object explorer window and a summary window. We shall examine what each of these windows offer to the database administrator as we proceed with this tutorial series.

The Object Explorer

The Object Explorer is an integrated tool for viewing and managing objects in all server types. Objects can be viewed by their type or schema; they can be filtered by all or part of a name, schema or date; they can be asynchronously populated and filtered based on their metadata; SQL Server Agent on replication servers can be accessed and administered. Most of the objects in the object explorer are there for the use of the developer or the administrator. Some of these tools can be used with all server components while others can be used with specific components only.

The Object Explorer itself can be used to browse servers, create and locate objects, manage data sources and view logs. Specific editor windows connected to databases can be opened from the object explorer. Servers can be registered or registered servers can be managed for storing connection information. The Solution explorer stores and organizes scripts and related connection information called SQL Server Scripts. The SQL Server Templates can be used to create queries based on existing templates or create custom queries. Finally the Dynamic help displays a list of help topics when a component type or code is clicked.

The Object Explorer window is visible in the Management Studio interface by default. If it is not visible, click on the view menu to activate it.

A precondition to the use of the Object Explorer is that it must be connected to a server. To connect to a server click on connect in Object Explorer toolbar and choose the server from the drop down list.

Click on Database Engine… and the Connect to server dialog box opens. Select the server from the drop down list. Since there is only one registered server only one is displayed. However, the drop down list has an option ‘Browse for servers…’. On clicking the option another dialog box opens to display the servers available on the system. The server can be selected from the displayed list if required.

A number of optional settings are also available in the Object Explorer. If additional settings are specified while connecting to the server through the Connect to Server dialog box, the last used settings will be saved and any new connections made thereafter will use the settings as default. Optional connection settings can be specified as under:

  • Click Connect on the Object Explorer toolbar, and click the type of server to connect to. The Connect to Server dialog box appears.

  • In the Server Name box, type the name of your SQL Server instance.
  • Click Options. The Connect to Server dialog box displays additional options.

  • Click the Connection Properties tab to configure the additional settings. The settings that are available vary depending upon the server type. The following settings are available for the Database Engine.

The Connect to Database allows users choose from the available set of databases on the server. The user must have the permission to view the database, else it will not be displayed. The Network protocol can be selected from shared memory, TCP/IP or Named Pipes. The Network Packet size is configured in bytes. By default this is set at 4096. Connection timeout is configured in seconds and the default figure is 15 seconds. The Execution timeout is also configured in seconds and the default is 0. If Encrypt connection is checked the encryption is enforced.

Servers can be registered from the Object Explorer. Right click server name and then click Register.

The Register server dialog box opens. In the dialog box specify the server and indicate where it has to be located in the group tree. The server name can also be replaced with a more meaningful server name.

New server groups can be created by clicking on New Group… button in the Register server dialog box illustrated above. Another dialog box opens asking the user enter a name and description for the group.

The object explorer window displays a tree view menu. The items in the menu can be right clicked to perform common tasks. The objects can be double clicked to expand them and display their nodes. Clicking on the (-) will collapse the tree. The population of the nodes can be stopped by clicking on Stop. The list of nodes displayed can be filtered.

It is to be pointed out that the contents of the tree do not get automatically refreshed. To refresh a view right click the object and click on refresh.

By default the object browser can display 65,536 objects. When the number of objects exceeds the limit, the user will have to close some of the nodes or apply filtering to view the objects that lie beyond the default number of objects that can be displayed.

The filter feature of the object explorer is intended to help the user filter the number of objects on some predefined criteria.

1. Click on the folder to be filtered and then click the filter button to open the filter settings dialog box.

2. In the filter settings dialog box filters can be set by Name, creation date, and schema and additional filtering operators like ‘starts with’, ‘contains’, ‘Between’ can be set.

Normally, it is possible to select only one object at a time in Object Explorer. Multiple items can be selected if the user presses F7 and opens Summary Page.

A connected query editor can be opened from the object browser by right clicking the server name in the Object Explorer and clicking New Query. To open a query editor for a specific database, the database name has to be right clicked and New Query will have to be clicked. For Analysis Services query the user can select DMX, MDX or XMLA queries.

« « Oracle Reports 6i
Using SQL Sever Management Studio – Part 2 » »

Author Description

Avatar

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

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • SQL Server 2005 – Using Event Notifications

    December 13, 2005 - 0 Comment
  • XML Data Types in SQL Server 2005

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

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Planning to Install SQL Server 2005

    November 29, 2005 - 0 Comment
  • Overview of SQL Server 2005 for the Database Developer

    September 8, 2005 - 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