/************************************
Chapter 11 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

SELECT * from sys.transmission_queue

/************************************/

USE Master;
GO
CREATE DATABASE CH11SB1
WITH TRUSTWORTHY ON

/************************************/

USE Master;
GO
CREATE DATABASE CH11SB2
WITH TRUSTWORTHY ON

/************************************/

USE CH11SB1;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
USE CH11SB2;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'P@ssw0rd';

/************************************/

USE CH11SB1;
GO
CREATE MESSAGE TYPE [mySendMsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [myReceiveMsg]
VALIDATION = WELL_FORMED_XML;
GO
USE CH11SB2;
GO
CREATE MESSAGE TYPE [mySendMsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [myReceiveMsg]
VALIDATION = WELL_FORMED_XML;

/************************************/

USE CH11SB1;
GO
CREATE CONTRACT myContract
(mySendMsg SENT BY INITIATOR,   
myReceiveMsg SENT BY TARGET );
GO
USE CH11SB2;
CREATE CONTRACT myContract
(mySendMsg SENT BY INITIATOR,   
myReceiveMsg SENT BY TARGET );

/************************************/

USE CH11SB1;
GO
CREATE QUEUE FirstQueueDB1
WITH STATUS = ON;
GO
USE CH11SB2;
GO
CREATE QUEUE SecondQueueDB2
WITH STATUS = ON;

/************************************/

USE CH11SB1;
GO
CREATE SERVICE SB1Service1
ON QUEUE FirstQueueDB1 (myContract);
GO
USE CH11SB2;
GO
CREATE SERVICE SB2Service2
ON QUEUE SecondQueueDB2 (myContract);

/************************************/

USE CH11SB1;
GO
DECLARE @ConversationHandle uniqueidentifier;
DECLARE @XMLmsg xml;
--Start the conversation
BEGIN DIALOG CONVERSATION @ConversationHandle
      FROM SERVICE SB1Service1
      TO SERVICE 'SB2Service2'
      ON CONTRACT myContract;
SET @XMLmsg = '<MCITPSuccess/>';
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE mySendMsg(@XMLmsg);

/*************************************/

USE CH11SB2;
GO
SELECT * from SecondQueueDB2;

/************************************/

USE CH11SB2;
GO
DECLARE @receivedMsg XML; 
DECLARE @ConversationHandle uniqueidentifier; 
RECEIVE TOP(1) @receivedMsg = message_body,
               @ConversationHandle = conversation_handle
FROM SecondQueueDB2
SELECT @receivedMsg;
/*At this point the application can do something with the message.  
For example, if the message is MCITPSuccess we could fire a stored 
procedure named usp_Success.  Of course, as an XML message we could have 
much more than just a one line message.  The logic is limited only by the 
imagination of the database developer.*/
DECLARE @returnMsg XML; 
SET @returnMsg = '<Success/>';
SEND ON CONVERSATION @ConversationHandle
      MESSAGE TYPE myReceiveMsg(@returnMsg);

/************************************/

USE CH11SB1;
GO
SELECT * FROM FirstQueueDB1;

/************************************/

USE CH11SB1;
GO
DECLARE @receivedMsg XML; 
DECLARE @ConversationHandle uniqueidentifier; 
RECEIVE TOP(1) @receivedMsg = message_body,
               @ConversationHandle = conversation_handle
FROM FirstQueueDB1
SELECT @receivedMsg;

/************************************/

USE CH11SB1;
GO
SELECT * from sys.transmission_queue
GO
USE CH11SB2;
GO
SELECT * from sys.transmission_queue

/************************************/

USE CH11SB1;
GO
DECLARE @ConversationHandle uniqueidentifier
SELECT TOP(1) @ConversationHandle = conversation_handle
FROM sys.conversation_endpoints;
END CONVERSATION @ConversationHandle;
GO
USE CH11SB2;
GO
DECLARE @ConversationHandle uniqueidentifier
SELECT TOP(1) @ConversationHandle = conversation_handle
FROM sys.conversation_endpoints;
END CONVERSATION @ConversationHandle
WITH CLEANUP

/************************************/

SELECT * FROM 
sys.event_notification_event_types

/************************************/

USE Master;
GO
CREATE DATABASE EventNotifications;

/*************************************/

USE EventNotifications;
GO
ALTER DATABASE EventNotifications SET ENABLE_BROKER;

/************************************/

CREATE TABLE LogEvents
 (
 Command NVARCHAR(1000), 
 PostTime NVARCHAR(24), 
 HostName NVARCHAR(100), 
 LoginName NVARCHAR(100) 
 ) 

/************************************/

CREATE QUEUE AuditQueue WITH STATUS = ON

/************************************/

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

/************************************/

CREATE ROUTE rtNotify 
WITH SERVICE_NAME = 'AuditService', 
ADDRESS = 'LOCAL'

/************************************/

CREATE EVENT NOTIFICATION Audit_CREATE_TABLE_Events 
ON DATABASE                                       
FOR CREATE_TABLE              
TO SERVICE 'AuditService','current database' 

/************************************/

SELECT * FROM AuditQueue

/************************************/

CREATE TABLE MCITP
(
Success char
)

/************************************/

SELECT * FROM AuditQueue

/*************************************/

SELECT CAST(message_body AS xml) FROM AuditQueue

/************************************/
--Declare variables to pull data from queue
DECLARE @messageTypeName NVARCHAR(256), @messageBody XML ;
--Pull data from queue
RECEIVE TOP(1) 
     @messageTypeName = message_type_name, 
     @messageBody = CAST(message_body AS xml)
   FROM AuditQueue; 
IF @@ROWCOUNT = 0       --Nothing in queue
      RETURN            --Exit
--Declare variables to INSERT data into LogEvents table
DECLARE @cmd NVARCHAR(1000), @posttime NVARCHAR(24), @spid NVARCHAR(6)
DECLARE @hostname NVARCHAR(100), @loginname NVARCHAR(100) 
--Populate variables
SET @cmd = CONVERT(NVARCHAR(100),
      @messagebody.query('data(//TSQLCommand//CommandText)')) 
SET @posttime = CONVERT(NVARCHAR(24),@messagebody.query('data(//PostTime)')) 
SET @spid = CONVERT(NVARCHAR(6),@messagebody.query('data(//SPID)')) 
SET @hostname = HOST_NAME() 
SET @loginname = SYSTEM_USER 
--Insert data into LogEvents table
INSERT INTO LogEvents(Command,PostTime,HostName,LoginName) 
VALUES(@cmd, @posttime, @hostname, @loginname) 

/************************************/

SELECT * FROM LogEvents

/************************************/

GRANT connect ON ENDPOINT::endPointName TO login;

/************************************/

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.GetProductList 
AS
BEGIN
      SELECT ProductID, Name, ProductNumber, ListPrice 
            FROM Production.Product
            WHERE ListPrice > 0
END

/************************************/

EXEC dbo.GetProductList

/************************************/

CREATE ENDPOINT AW_Products
    STATE = Started
AS HTTP
    (
        PATH = '/Products',
        AUTHENTICATION = (INTEGRATED),
        PORTS = (CLEAR),
        SITE = '*'
    )
FOR SOAP
    (
        WEBMETHOD 'GetProductList'
            (NAME = 'AdventureWorks.dbo.GetProductList'),
        WSDL = DEFAULT,
        DATABASE = 'AdventureWorks',
        NAMESPACE = DEFAULT
    )

/************************************/

/* Add this to the beginning of the class*/
using System.Net;
/*Add these lines to the button click event procedure*/localhost.AW_Products Products = new localhost.AW_Products();
Products.Credentials = CredentialCache.DefaultCredentials;
dataGridView1.DataSource = (Products.GetProductList()[0] as _   
               DataSet).Tables[0];

/************************************/

USE master;
GO
EXEC sp_addlinkedserver 
   'MCITP2',
   'SQL Server'

/*************************************/

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers)
GO

/************************************/

SELECT * from MCITP2.AdventureWorks.Sales.SalesOrderHeader

/************************************/