/************************************
Chapter 12 Scripts
For MCITP SQL Server 2005 Database Administration 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.

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

SELECT * from sys.transmission_queue

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

USE Master;
GO
CREATE DATABASE CH12SB1
WITH TRUSTWORTHY ON

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

USE Master;
GO
CREATE DATABASE CH12SB2
WITH TRUSTWORTHY ON

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

USE CH12SB1;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
USE CH12SB2;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'P@ssw0rd';

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

USE CH12SB1;
GO
CREATE MESSAGE TYPE [mySendMsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [myReceiveMsg]
VALIDATION = WELL_FORMED_XML;
GO
USE CH12SB2;
GO
CREATE MESSAGE TYPE [mySendMsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [myReceiveMsg]
VALIDATION = WELL_FORMED_XML;

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

USE CH12SB1;
GO
CREATE CONTRACT myContract
(mySendMsg SENT BY INITIATOR,   
myReceiveMsg SENT BY TARGET );
GO
USE CH12SB2;
CREATE CONTRACT myContract
(mySendMsg SENT BY INITIATOR,   
myReceiveMsg SENT BY TARGET );
GO

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

USE CH12SB1;
GO
CREATE QUEUE FirstQueueDB1
WITH STATUS = ON;
GO
USE CH12SB2;
GO
CREATE QUEUE SecondQueueDB2
WITH STATUS = ON;
GO

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

USE CH12SB1;
GO
CREATE SERVICE SB1Service1
ON QUEUE FirstQueueDB1 (myContract);
GO
USE CH12SB2;
GO
CREATE SERVICE SB2Service2
ON QUEUE SecondQueueDB2 (myContract);

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

USE CH12SB1;
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 CH12SB2;
GO
SELECT * from SecondQueueDB2;

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

USE CH12SB2;
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.  Since it is 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 CH12SB1;
GO
SELECT * FROM FirstQueueDB1;

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

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

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

USE CH12SB1;
GO
SELECT * from sys.transmission_queue
GO
USE CH12SB2;
GO
SELECT * from sys.transmission_queue

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

USE CH12SB1;
GO
DECLARE @ConversationHandle uniqueidentifier
SELECT TOP(1) @ConversationHandle = conversation_handle
FROM sys.conversation_endpoints;
END CONVERSATION @ConversationHandle;
GO
USE CH12SB2;
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 = 'svcNotify', 
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

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

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
    )

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

Rem this can be pasted into a batch file

bcp AdventureWorks.Sales.Currency out Currency.dat -T -c

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

Rem this can be pasted into a batch file

Bcp AdventureWorks.Production.Product out products.txt –T –c

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

Rem this can be pasted into a batch file

Notepad products.txt

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

Rem this can be pasted into a batch file

Bcp AdventureWorks.Production.Product out products.txt –T –c –t;

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

Rem this can be pasted into a batch file

bcp "SELECT Title, FirstName, LastName, EmailAddress FROM 
  AdventureWorks.Person.Contact" queryout emaillist.txt –T –c

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

Rem this can be pasted into a batch file

Notepad emaillist.txt

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

Rem this can be pasted into a batch file

bcp "SELECT Title, FirstName, LastName, EmailAddress FROM 
  AdventureWorks.Person.Contact" queryout emaillist.txt –T –c -t,

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

Rem this can be pasted into a batch file

bcp "SELECT Title, FirstName, LastName, EmailAddress FROM  
  AdventureWorks.Person.Contact ORDER BY LastName" 
  queryout emaillist.txt –T –c –t,

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

Rem this can be pasted into a batch file

Notepad emaillist.txt

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

USE Master;
GO
CREATE Database bcpImport

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

USE bcpImport;
GO
CREATE TABLE dbo.emailList
(
	Title nvarchar(6) NULL, 
	FirstName nvarchar(50)  NULL, 
	LastName nvarchar(50)  NULL, 
	EMail nvarchar(50)  NULL, 
);

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

Bcp bcpImport.dbo.emailList in emaillist.txt –T –c –t,

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

USE bcpImport;
GO
SELECT * FROM emailList

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

CREATE TABLE dbo.BulkInsert
(
	Title nvarchar(6) NULL, 
	FirstName nvarchar(50)  NULL, 
	LastName nvarchar(50)  NULL, 
	email nvarchar(50)  NULL, 
);

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

BULK INSERT bcpImport.dbo.BulkInsert
 FROM 'c:\emaillist.txt'
   WITH 
     ( FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n' )

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

SELECT *
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\data\Sales.mdb';
      'admin';'',Customers)
GO

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

SELECT * FROM 
   OPENDATASOURCE('SQLNCLI',
    'Data Source=MCITP1\MyNamedInstance;Integrated Security=SSPI')
    .AdventureWorks.Person.Contact