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 Event Notifications

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

SQL Server 2005 – Using Event Notifications

In this tutorial you will learn about SQL Server 2005 – Using Event Notifications, Creating Event Notifications, Creating an Event Notification, Creating the Target Service, Creating /Dropping / modifying the Event Notification and Using Metadata Views.

DDL statements or SQL Trace events execute Event Notifications. The information is sent to a Service Broker Service. These event notifications help in logging and reviewing changes occurring in databases. They perform action in response to an event in an asynchronous manner. They can be said to offer a programming alternative to DDL triggers and SQL Trace.

Event notifications can be used inside database applications to respond to events, operating outside the scope of the transaction. In this Event notification are different from DDL triggers.

Event notifications perform actions inside an SQL Server in response to a SQL Trace Event. In this they are different from SQL Trace.

Event notifications open one or more Service Broker conversations between the SQL Server and the target service. These conversations also continue so long as the event notification exists as an object in the server instance and are never shared between event notifications. The target service will cease to receive messages when the event notification ends and the target service will not be opened the next time the event notification is fired.

The variable type xml is used to deliver Event information to the Service Broker. The information specifies the details of the event, the database object impacted, the T-SQL statement and other required information.

Creating Event Notifications

While designing event notifications it is important for the DBA to define the scope of the notification and the type of T-SQL statement(s) that will fire the notification.

The event notification can be defined to respond to a statement made on the objects in a database or on all object on an instance of the SQL Server. Event notifications that can be scoped on individual queues are QUEUE_ACTIVATION and BROKER_QUEUE_DISABLED. CREATE _DATABASE events run only on server instance level while ALTER_TABLE event can run on all tables in the database.

An event notification can be designed to fire after a particular Transact-SQL statement is run or after a SQL Trace event belonging to a predefined grouping of similar trace events is run.

Creating an Event Notification:

An event notification involves creation of a target service to the event notification and the process of creating the event notification.

Creating the Target Service

1. Create a queue to receive messages.
2. Create a service on the queue that references the event notifications contract.
3. Create a route on the service to define the address to which Service Broker sends messages for the service. If the event notification targets a service in the same database, the DBA must specify ADDRESS = ‘LOCAL’.

CREATE QUEUE ExforsysQueue ;
GO
CREATE SERVICE ExforsysService
ON QUEUE ExforsysQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE ROUTE ExforsysRoute
WITH SERVICE_NAME = ‘ExforsysService’,
ADDRESS = ‘LOCAL’;
GO

Creating /Dropping / modifying the Event Notification

Transact-SQL CREATE EVENT NOTIFICATION statement is used for creating Event notifications. The Event notification can be dropped using the DROP EVENT NOTIFICATION STATEMENT. It can be modified by dropping and recreating it only.

Event notifications can be created on remote servers by defining routes on both the source and the target server for two way communication. It is necessary to enable the two instances of SQL Server to connect to each other by creating Service Broker end points on both instances.

Using Metadata Views

Information about views can be derived if the view is not encrypted. Views can be queried in the same way that tables are queried. However, any table hints will be ignored. Changing the name of the object referenced in a view will require a modification of the view to make the text reflect the new name. To get information about a view the following statements can be used.

sys.views (Transact-SQL)

sys.columns (Transact-SQL)

sp_helptext (Transact-SQL)

To view the data defined by a view

SELECT (Transact-SQL)

To display the dependencies of a view

sys.sql_dependencies (Transact-SQL)

Metadata of views can also be obtained by using Information Schema views. These views can be used to garner information regarding internal, system-table independent view of the SQL Server metadata. These views allow applications to work properly despite changes being made to the system tables. They conform to the SQL 92 standard definition for the INFORMATION_SCHEMA.

The SQL -92 standard supports three part naming convention just like SQL Server. However the naming convention in SQL Server 2005 is slightly different from SQL 92 and are defined in as special schema called INFORMATION_SCHEMA which contains metadata for all data objects stored in the database.

Some views contain references to different classes of data which include character data and binary data. The syntax for viewing metadata on views is as under:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM Exforsys.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N’Product’;
GO

In this lesson we have examined in detail the monitoring tools of SQL Server 2005. In the lessons that follow we shall see how the DBA can maintain databases and indexes.

« « Servlets Advanced
Oracle Reports 6i » »

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 Architecture and Components

    September 13, 2005 - 0 Comment
  • SQL Server 2005 Configuration Manager

    December 18, 2005 - 0 Comment
  • Developing Client applications in SQL Server 2005

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

    December 26, 2005 - 0 Comment
  • 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
  • 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