Friday, 30 May 2008

Compressing Hyper-V disk images (.vhd files)

How to compress Hyper-V disk images (.vhd files) >

1) Go to Server Manager.

2) Shutdown the Virtual Machine in Hyper-V Manager.

3) Select the VM and click Settings in the right hand pane.


4) Click the hard drive and select 'Edit'.


5) Select 'Compact' (default action) and click 'finish'.


Wednesday, 28 May 2008

Name Table Constraints

When creating constraints, NAME THEM !

For example ;
ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL DEFAULT ((17.5))

Gives a randomly named constraint >

DF__Orders__Tax__42E0813E

Not a great problem in itself, it's not like you'll need to look at the name of the constraint is it?

WRONG!
Comparison tools (in my case SQL Compare) will compare otherwise identical tables and report differences.

It is far safer therefore, to explicitly name the constraint -
ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL CONSTRAINT [DF_tax] DEFAULT ((17.5))

Further Reading -
http://www.sqlteam.com/article/default-constraint-names

Tuesday, 27 May 2008

12 Tips for Optimising SQL Server 2005 Queries

1. Turn on the execution plan, and statistics

2. Use Clustered Indexes

3. Use Indexed Views

4. Use Covering Indexes

5. Keep your clustered index small.

6. Avoid cursors

7. Archive old data

8. Partition your data correctly

9. Remove user-defined inline scalar functions

10. Use APPLY

11. Use computed columns

12. Use the correct transaction isolation level


A well written article to jog the memory when writing TSQL >

http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

Saturday, 24 May 2008

SQL Beautifier

Online SQL code beautifier -

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Brilliant for tidying and standardising your code.

Is similar to the SQL Prettifier I found last year.

Tuesday, 20 May 2008

Sending Service Broker Message on Trigger

Sample Trigger to send Service Broker message on data change
CREATE TRIGGER [Person].[TRG_Person_Contact]
ON [Person].[Contact]
FOR DELETE,INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @XMLDOC XML
DECLARE @XMLTEXT NVARCHAR(MAX)

DECLARE @ContactID [int]
DECLARE @NameStyle [bit]
DECLARE @Title [nvarchar] (8)
DECLARE @FirstName [nvarchar] (50)
DECLARE @MiddleName [nvarchar] (50)
DECLARE @LastName [nvarchar] (50)
DECLARE @Suffix [nvarchar] (10)
DECLARE @EmailAddress [nvarchar] (50)
DECLARE @EmailPromotion [int]
DECLARE @Phone [nvarchar] (25)
DECLARE @PasswordHash [varchar] (128)
DECLARE @PasswordSalt [varchar] (10)
DECLARE @AdditionalContactInfo [xml]
DECLARE @rowguid [uniqueidentifier]
DECLARE @ModifiedDate [datetime]

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

-- This cursor for inserted or updated records

IF @ACT = 'INSERT' OR @ACT = 'UPDATE'
BEGIN
DECLARE curINSERTED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[INSERTED].[ContactID]
,[INSERTED].[NameStyle]
,[INSERTED].[Title]
,[INSERTED].[FirstName]
,[INSERTED].[MiddleName]
,[INSERTED].[LastName]
,[INSERTED].[Suffix]
,[INSERTED].[EmailAddress]
,[INSERTED].[EmailPromotion]
,[INSERTED].[Phone]
,[INSERTED].[PasswordHash]
,[INSERTED].[PasswordSalt]
,[INSERTED].[AdditionalContactInfo]
,[INSERTED].[rowguid]
,[INSERTED].[ModifiedDate]
FROM
[INSERTED]

OPEN curINSERTED
FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curINSERTED
DEALLOCATE curINSERTED
END

-- This cursor for deleted records

IF @ACT = 'DELETE'
BEGIN
DECLARE curDELETED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[DELETED].[ContactID]
,[DELETED].[NameStyle]
,[DELETED].[Title]
,[DELETED].[FirstName]
,[DELETED].[MiddleName]
,[DELETED].[LastName]
,[DELETED].[Suffix]
,[DELETED].[EmailAddress]
,[DELETED].[EmailPromotion]
,[DELETED].[Phone]
,[DELETED].[PasswordHash]
,[DELETED].[PasswordSalt]
,[DELETED].[AdditionalContactInfo]
,[DELETED].[rowguid]
,[DELETED].[ModifiedDate]
FROM
[DELETED]

OPEN curDELETED
FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curDELETED
DEALLOCATE curDELETED
END

END
go

Monday, 19 May 2008

USP_ReceiveResponseAndCloseConversation

Procedure to tidy receiving & responding to a service broker message.
Requires TBL_ServiceBrokerAudit for optional message auditing. -

CREATE PROCEDURE [dbo].[USP_ReceiveAndRespondBrokerMessage]
@Queue NVARCHAR(200),
@MessageType NVARCHAR(200),
@ResponseType NVARCHAR(200),
@ResponseMessage NVARCHAR(4000),
@Audit BIT

AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Message

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
       @RecvReqMsg XML OUTPUT,
       @RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
    ( RECEIVE TOP(1)
     @RecvReqDlgHandle = conversation_handle,
     @RecvReqMsg = message_body,
     @RecvReqMsgName = message_type_name
      FROM [' + @Queue + ']
    ), TIMEOUT 1000;'

EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
     ([Queue]
     ,[MessageHandle]
     ,[MessageName]
     ,[MessageContent]
     ,[AuditSource])
  VALUES
     (@Queue
     ,@RecvReqDlgHandle
     ,@RecvReqMsgName
     ,@RecvReqMsg
     ,'USP_ReceiveAndRespondBrokerMessage')
END
-- Display received message
SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsg IS NOT NULL
BEGIN
-- If received message is of the correct type, send response
IF @RecvReqMsgName = @MessageType

BEGIN
  SET @SqlString = ' SEND ON CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + '''
        MESSAGE TYPE
       [' + @ResponseType + '] (''' + @ResponseMessage + ''');
      END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'

  EXEC sp_executesql @SqlString
  -- Uncomment next line to debug
  --SELECT @ResponseMessage AS SentReplyMsg;

END
ELSE
 SELECT 'Not Expected Message Type'
END
ELSE
SELECT 'No Message to Receive'
COMMIT TRANSACTION;

END
GO

Usage :
exec adventureworks.dbo.USP_ReceiveResponseAndCloseConversation
  @Queue = N'AdventureWorksSourceQueue'
 ,@Audit = 1
go

USP_ReceiveAndRespondBrokerMessage

Procedure to tidy service broker message response and conversation closure.
Requires TBL_ServiceBrokerAudit for optional message auditing.
CREATE PROCEDURE [dbo].[USP_ReceiveResponseAndCloseConversation]
@Queue NVARCHAR(200)
,@Audit BIT
AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Response

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
@RecvReqMsg XML OUTPUT,
@RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM [' + @Queue + ']
), TIMEOUT 1000;'



EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
([Queue]
,[MessageHandle]
,[MessageName]
,[MessageContent]
,[AuditSource])
VALUES
(@Queue
,@RecvReqDlgHandle
,@RecvReqMsgName
,@RecvReqMsg
,'USP_ReceiveResponseAndCloseConversation')
END

-- Uncomment next line to debug
-- SELECT @RecvReqMsg AS ReceivedRequestMsg;

SET @SqlString = ' END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'
EXEC sp_executesql @SqlString


COMMIT TRANSACTION;
END
GO

Usage :
exec USP_ReceiveAndRespondBrokerMessage
@Queue = N'AdventureWorksTargetQueue'
,@MessageType = N'http://www.servicebroker.com/SendType'
,@ResponseType = N'http://www.servicebroker.com/ResponseType'
,@ResponseMessage = N'Reply Service Broker Message from AdventureWorksTarget'
,@Audit = 1
go