Monday, October 03, 2011

A Performance Troubleshooting Methodology for SQL Server

A Performance Troubleshooting Methodology for SQL Server

Going Beyond the Relational Model with Data

Going Beyond the Relational Model with Data

Thursday, September 22, 2011

Build Hierarchy table from core table

if(object_id('tempdb..#tmp')>1) drop table #tmp declare @depth int = 0 , @count int = 0 create table #tmp (parent_id int, child_id int, depth_from_parent int) SET @depth = 0; TRUNCATE TABLE #tmp; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) VALUES (1, 1, @depth) WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1 INSERT INTO #tmp(parent_id, child_id, depth_from_parent) OUTPUT inserted.child_id, inserted.child_id, 0 INTO #tmp SELECT 1, ou.ou_id, os.depth_from_parent + 1 FROM #tmp os (NOLOCK) INNER JOIN ou (NOLOCK) ou ON os.child_id = ou.parent_id WHERE os.depth_from_parent = @depth - 1; END SET @depth = 0 WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) SELECT os.parent_id, ou.ou_id, @depth FROM #tmp os INNER JOIN ou (NOLOCK) ON os.child_id = ou.parent_id --AND os.parent_id <> ou.ou_id -- exclude circular reference WHERE os.depth_from_parent = @depth - 1 AND os.parent_id <> 1 END

Wednesday, August 03, 2011

minimum account permissions for service broker

USE USERDB
go

CREATE SCHEMA [office\user] AUTHORIZATION [office\user];

GO
CREATE USER [office\user] WITH DEFAULT_SCHEMA = [office\user];
GRANT CREATE PROCEDURE TO [office\user];
GRANT CREATE QUEUE TO [office\user];
GRANT CREATE SERVICE TO [office\user];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [office\user];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [office\user];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [office\user];
GRANT CONTROL ON SCHEMA::[dbo] TO [office\user];
GRANT IMPERSONATE ON USER::DBO TO [office\user];
GO