Further to the Google Blog

I used to think it was fantasy that the memory starts going as you get older. Then I though it was a curse, as it got tougher to answer questions of the type which once would have been easy. Then life became hell when everyone seemed to become a developer of a new language. All they had to do was create a bunch of tags to reference some data and as long as they used the tags consistently then they had created labels that could be used to refer to that data. The use of these labels could facilitate communication between two parties in a controlled fashion (language).  

This is obviously one of the features which gives XML its power, the ability to define your own reality! However, if there is no control over this flexibility then you descend into hell as soon as you press the ‘<‘ and you have a bad memory.

So we have schemas (descriptions of corresponding XML documents, themselves written in XML) which help people to communicate what can be represented by the tags. One can even use comments to make the tags understandable to the human reader of the schema. However the schema is primarily used by a machine to ensure that the corresponding document is valid in terms of structure, if not necessarily content.

This is great if you get given an XML document and you have to read it. You can refer to the schema when you don’t understand a tag to see what other things it could contain to gain an idea of the context of the value within the tag. However, XML is more commonly used every day, and when it is used as a control for a configuration file then a feature which would be more useful is a list of the values that can be inserted at a given context within an XML document.

Well this has been possible in Visual Studio (VS) previously and if you have used it there then you miss it if it is not possible to do in an XML editor window within SQL Server Management Studio (SQLMS).

So thanks Bill for the question which prompted the search for the answer to your question which led to me sharing this with others that may not be aware either of the fact that this aid is available in VS or that it can be extended to SQLMS.

"Is it possible to give the XML document editor in SQLMS intellisense like it is available in VS."?

Of course the answer is yes and the process is:-

1) Identify the directory that contains the schemas that SSQMS is aware of . You will find this inside Tools/Options/Text/Miscellaneous. The expanded name for the default setup is ‘C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Xml’

2) Within this directory identify the catalog.xml file.

3) Identify the directory that contains the Schemas of interest. In this example we will use the schema directory created by Microsoft for Notification services of which the default location is   ‘C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\XML’  and identify the XML Schemas that you are interested in 

4) Take the schema description information from the file of interest

      and insert it into the catalog.xml file (See step 2).  

5) Open a corresponding XML file and edit it. Notice the drop down list box when you type ‘<‘.

6) This is what is being referred to as Intellisense! The contents of the drop down list box changes based on the location where you are inserting a new tag/ label.

 7) Now that is useful wouldn’t you agree 😉 Go Play!

— Automating Event Monitoring using templates

ALTER DATABASE <Database, varchar(50),WhichDatabase?> SET ENABLE_BROKER;

USE <Database, varchar(50),WhichDatabase?>

GO

— Drop and create the eventLog table

IF exists (select * from dbo.sysobjects where id = object_id(N’dbo.EventLog’) and OBJECTPROPERTY(id, N’IsTable’) = 1)

DROP TABLE dbo.EventLog
GO

CREATE TABLE EventLog
(

Command nvarchar(1000),
PostTime nvarchar(24),
HostName nvarchar(100),
LoginName nvarchar(100)

)
GO

— Create a queue

CREATE QUEUE Queue_EventNotification
GO

— create an event processing service

CREATE SERVICE Service_EventNotification
ON QUEUE Queue_EventNotification
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)

GO

— Create a route

CREATE ROUTE Route_EventNotification

WITH SERVICE_NAME = ‘Service_EventNotification’,
ADDRESS = ‘LOCAL’
GO

— Create the database event notifications

CREATE EVENT NOTIFICATION NotifyCREATE_TABLE
ON DATABASE

FOR CREATE_TABLE
TO SERVICE ‘Service_EventNotification’, ‘current database’
GO

CREATE EVENT NOTIFICATION NotifyALTER_TABLE
ON DATABASE

FOR ALTER_TABLE
TO SERVICE ‘Service_EventNotification’, ‘current database’
GO

–create a table

CREATE TABLE T1 (col1 int)
GO

— Process the event notifications

DROP PROCEDURE usp_ProcessEventNotification
GO

CREATE PROCEDURE usp_ProcessEventNotification
AS
DECLARE @messageTypeName nvarchar(256)
, @messageBody xml
;RECEIVE TOP(1)

@messageTypeName = message_type_name,
@messageBody = message_body

FROM dbo.Queue_EventNotification;

IF @@ROWCOUNT = 0

RETURN

PRINT CONVERT(nvarchar(1000),@messagebody)

DECLARE @cmd nvarchar(1000)
DECLARE @posttime nvarchar(24)
DECLARE @spid nvarchar(6)
DECLARE @hostname nvarchar(100)
DECLARE @loginname nvarchar(100)

SET @cmd = @messagebody.value
(
‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’
, ‘nvarchar(1000)’
)

SET @posttime = @messagebody.value (‘(/EVENT_INSTANCE/PostTime)[1]’, ‘nvarchar(24)’)

SET @spid = @messagebody.value (‘(/EVENT_INSTANCE/SPID)[1]’, ‘nvarchar(6)’)

SET @loginname = @messagebody.value (‘(/EVENT_INSTANCE/LoginName)[1]’, ‘nvarchar(100)’)

SET @hostname = HOST_NAME()

INSERT INTO EventLog(Command,PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)
GO

EXECUTE usp_ProcessEventNotification

— SELECT * FROM EventLog — Uncomment to test

— Alter the table

ALTER TABLE T1 ADD col2 int
GO

— process the event notifications

Exec usp_ProcessEventNotification

— SELECT * FROM EventLog — Uncomment to test 

— Clean up the environment, comment out once you have created the

— environment that you want.

DROP EVENT NOTIFICATION NotifyCREATE_TABLE ON DATABASE
DROP EVENT NOTIFICATION NotifyALTER_TABLE ON DATABASE
DROP ROUTE Route_EventNotification
DROP SERVICE Service_EventNotification
DROP QUEUE Queue_EventNotification
DROP TABLE T1
DROP TABLE EventLog

—             This first query

SELECT   name

              , endpoint_id

              , protocol_desc

              , type_desc

FROM SYS.ENDPOINTS

—  Shows you a static report of the the ports that are available for communication on the server

name                                           endpoint_id  protocol_desc              type_desc

—————————————           —————   ——————-               ————-

Dedicated Admin Connection           1                TCP                            TSQL     

TSQL Local Machine                      2                SHARED_MEMORY      TSQL      

TSQL Named Pipes                        3                 NAMED_PIPES            TSQL     

TSQL Default TCP                         4                 TCP                            TSQL     

TSQL Default VIA                         5                 VIA                            TSQL     

(5 row(s) affected)

—             This query

SELECT   session_id

              , net_transport

              , local_net_address

              , local_tcp_port

FROM MASTER.SYS.DM_EXEC_CONNECTIONS

/*           

     Shows you the connections that are currently being used including

     the TCP Port number being utilised in the case of a dynamically

     assigned one for a connected server instance.

*/

session_id       net_transport      local_net_address     local_tcp_port

—————      ——————–     ————————-     ——————–

51                  Shared memory    NULL                      NULL

52                  TCP                   192.168.131.69           1267

53                  Shared memory    NULL                      NULL

(3 row(s) affected) — This first query