How to retrieve data recursively

There are many times where we need to retrieve hierarchy data in sql. Like given a manager displaying all the employees below him for many levels. Or given a product, to find all the sub-parts and sub-parts for the Sub-parts. We need a recursive function to display the data. In Sql server 2005 we can acheive this using Common Table Expression. For understanding this check the following example.

Create the employee table with manager id and insert few records.


CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)


INSERT INTO Employees VALUES(1 , NULL, 'Nancy' )
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' )
INSERT INTO Employees VALUES(3 , 1 , 'Janet' )
INSERT INTO Employees VALUES(4 , 1 , 'Margaret')
INSERT INTO Employees VALUES(5 , 2 , 'Steven' )
INSERT INTO Employees VALUES(6 , 2 , 'Michael')
INSERT INTO Employees VALUES(7 , 3 , 'Robert' )
INSERT INTO Employees VALUES(8 , 3 , 'Laura' )
INSERT INTO Employees VALUES(9 , 3 , 'Ann' )
INSERT INTO Employees VALUES(10, 4 , 'Ina' )
INSERT INTO Employees VALUES(11, 7 , 'David')
INSERT INTO Employees VALUES(12, 7 , 'Ron' )
INSERT INTO Employees VALUES(13, 7 , 'Dan' )
INSERT INTO Employees VALUES(14, 11 , 'James' )
GO

/*The following displays all the employees who are under that manager till the last level of employee. */

Declare @empid int
set @empid = 3 /*Any manager's employee id*/

WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid /* Managers employee Id */
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
SELECT * FROM Employees_Subtree
GO

If we need to get the manager Name just add a self join to employees table.

Sql Server Reporting Services - Errors and Solutions

Error - 1

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

Solution:
Check the microsoft KB article at the following link.
http://support.microsoft.com/kb/842421

Error -2
The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

Solution:
Apply the permissions to the user as mentioned in the below article.
http://msdn2.microsoft.com/en-us/library/aa337471.aspx
If you are using Team Foundation Server, check the following link.
http://team-foundation-server.blogspot.com/2007/09/permissions-granted-to-user-userid-are.html

How can we supress successful messages in the EventLog

When running backup batch for each successfull backup a successfull message is logged in the EventLog and and log file.

We can supress this message by using DBCC trace flag at the start of the batch and end of the batch. If a DBA wants to backup around 100 databases and not interested in success messages this is more helpfull.

You can add your backup statements in between DBCC statements as shown below.


DBCC TRACEON (3226)
GO
BACKUP DATABASE AdventureWorks to disk = N'C:\AdventureWorks.BAK'
GO
BACKUP DATABASE AdventureWorksDW to disk = N'C:\AdventureWorksDW.BAK'
GO
DBCC TRACEOFF (3226)
GO