SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL

Introduction

Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is -

Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee.

Problem Statement

This table structure very well serves the purpose as long as we have 1-Level hierarchy. However, if the hierarchy is of n'th level, the SELECT statement to fetch the records becomes more complex with this kind of table structure. Suppose, we want to fetch the complete TREE of a particular employee, i.e. list of all the employees who are directly or indirectly managed by a particular employee. How to do it……..?

Thanks to CTE’s for making the life a bit easier – as using them in a recursive manner, we can get the work done. Please follow this msdn link to see an implementation using recursive CTE.

Suggested Table Structure

Here, I have just included a new column [PATH]. It is of VARCHAR(MAX) type. I have taken it as VARCHAR(MAX) just to make sure the field is long enough to store the complete path. But one can assign appropriate size as per their system’s requirement.

The basic idea of the [path] column is to store the complete hierarchical path of any employee separated by a delimiter as under -

Calculating the new path is very simple. It’s just, {New Path} = {Parent Path} + {Self ID} + {Delimiter}

Now, suppose if I want to fetch all the employees who are directly or indirectly working under EmployeeID = 2, I can use the below tsql -

;WITH CTE AS (

SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]

UNION ALL    

SELECT 2 EmployeeID,1 ManagerID, '\1\2\' [Path]

UNION ALL    

SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]

UNION ALL    

SELECT 4 EmployeeID,2 ManagerID, '\1\2\4\' [Path]

UNION ALL    

SELECT 5 EmployeeID,4 ManagerID, '\1\2\4\5\' [Path]

)

SELECT

  *

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

We can use a simple logic to even find out the level of the Employee -

SELECT

  *,

  (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

2 is subtracted from the formula as the length of delimiter for Level-0 is 2.

Conclusion

Hope, this simple trick could save a lot of time for the ones who find themselves lost playing with the hierarchical data.

Last Execution Date Time of a Stored Procedure

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.
Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. Please note that this view has been introduced from SQL Server 2008.
The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.
So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -

USE DBName

GO

SELECT 

  O.name,

  PS.last_execution_time

FROM 

  sys.dm_exec_procedure_stats PS 

INNER JOIN sys.objects O 

  ON O.[object_id] = PS.[object_id] 

P.S. Please replace the DBName with the actual name of the Database.

The above script will return the name of all the cached stored procedure of the current database with their last execution time.

For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)

Moving MASTER Database

In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step.

Step 1: Query sys view and note down the existing location for MASTER database

USE MASTER 
GO 
SELECT 
NAME, 
PHYSICAL_NAME AS 'PhysicalFilePath', 
STATE_DESC AS 'DB Status' 
FROM SYS.MASTER_FILES 
WHERE NAME LIKE 'Mast%'

Step 2: Run alter database command and change the location for database files

ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MASTER, 
FILENAME= 'C:\SQLDB\Demo\Master.mdf' 
) 
GO 
ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MastLog, 
FILENAME= 'C:\SQLDB\Demo\MastLog.mdf' 
) 
GO 

Step 3: Stop SQL Server Service and move database files to new location

Step 4: Restart SQL Server Service, surprised ?

 

Step 5: This was expected, let’s see what errorlog has to say about this!

Refer the highlighted section, SQL Server service could not find the files. This is because we have moved that files to new location.

Step 6: Okay, so let’s go and change the start-up parameter. We can do this using Configuration manager.

Step 7: Right click on SQL Server service –> Properties –> Start-up Parameter

Step 8: Make correction in path for Master.mdf and Master.ldf

Step 9: Start SQL Server service, this time it will start.

You are done!!

Note: This is to be done when we have to do relocate databases to new drive, or file organization, or some error which force us to do this.

-- Hemantgiri S. Goswami

Moving MSDB to new location

In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step.

Step 1: Let’s query sys view and note down the location for database files

SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

Step 2: Run alter database and specify new location for database

SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.mdf'
)
GO

Step 3: Stop SQL Server service

Step 4: Once SQL Server service is stopped move MSDB database to new location
Step 5: Now, start SQL Server service. This time it will use the new path that we have configured in Step 2.
Note: If you have enabled and configure Database Mail, please make sure it works after you moved MSDB to new location.

Tomorrow, I will post about how to relocate Master database.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/)

Register for SQL Server 2012 Virtual Launch

`As we all are aware SQL Server 2012 virtual launch is scheduled after 60 hours roughly. Earlier I have discussed what’s new in SQL Server 2012 setup, MS has released SQL Server 2012 training kit and made it available for us to download. In recent past, I have blogged about a free eBook for SQL Server 2012 written by Ross Mistry ( Blog | Twitter ) and Stacia Misner ( Blog | Twitter) .

You will see lots of new and insightful information on new and enhanced features introduced in SQL Server 2012, this includes:

• AlwaysOn
• Automating AlwaysOn management
• New backup and restore capabilities
• ColumnStore index
• Semantic Search
• User-defined server roles
• Data quality service
• StreamInsight for SQL Server 2012
• Big data analytics and Hadoop
• T-SQL Enhancements
• SQL Server Data Tools (SSDT)
• SQL Azure

Sounds interesting ?

Get yourself register for the event, you will find the agenda of the event here.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/ )

How to hide Placeholder in SSRS 2008?

Introduction

For the ones who are working with SSRS, Placeholder is not new for them. So, today I will not go into explaining what is Placeholder, How and Where it is used, etc. I will straight away target the issue mentioned and try to explain a possible way to overcome that. However, for the ones who do not know about Placeholder, please visit this MSDN link.

Problem

Let’s first try to understand the problem I am talking about. Please have a look at Diag: 1-

Diag: 1

Here, as you have noticed, 4 different placeholders are used to make up the details for the Attribute column; 2 each for Labels & their respective values. Now, the requirement is such that the Labels & their Values for the Sub Category are to be shown only if the Value exists ELSE even the label should not be shown. This can be easily managed using the below expression for the Label & Value placeholders respectively -

Diag: 2

  Diag: 3

But the main issue here is the BLANK SPACE CREATED refer Diag: 1. There is not direct property available to Hide the Placeholder. Hence, the need arises to find out some work around.

Solution

The expected output is -

Diag: 4

To get the expected output, the main credit goes to the below Placeholder property -

Diag: 5

We just need to select HTML as the Markup type and then set the Value expression as under -

Diag: 6

And yes, we are done !

Conclusion

This was the solution I could manage to find out. There might exist other solutions as well. Please revert back if you are aware of any such solution by adding a comment below.

CRM 2011: How to launch a Dialog on click of custom ribbon button?

In this post I will explain how to launch a Dialog from Ribbon Button in Dynamics CRM. The requirement is like configure a custom ribbon button on custom entity which will launch a dialog (Process).

In this Example, I’ve used custom entity “Flight Route” contains a button called “Test Button” which will launch a dialog. Dialog will select a flight name from the option set and insert new note record on the selected flight route record.

Following are steps that demonstrate launch dialog from ribbon button.

Before to start with a launch dialog, we need to create
1. Create a dialog for “Flight Route” entity
· Select Solution.
· Select Processes.
· Create new process.



· Specify the Process name.
· Select Entity.
· Select “Activate as” as “Process” and “Category” as “Dialog”.
· Configure it as an on-demand process.
· For this dialog make sure that it should contain at least one
  “Prompt and Response”



2. Javascript Library as a web resource
· Create new Web resource” /javascripts/LaunchModalDialog.js”



· Click on Text Editor.
Insert below function in Text editor window.

   1: function LaunchModalDialog(dialogId,typeName,recordId)

   2: {

   3: var serverUrl = Xrm.Page.context.getServerUrl();

   4: recordId=recordId.replace("{", "");

   5: recordId=recordId.replace("}", "");

   6:  

   7: dialogId=dialogId.replace("{", "");

   8: dialogId=dialogId.replace("}", "");

   9:  

  10: // Load modal

  11: var serverUri = serverUrl +'/cs/dialog/rundialog.aspx';

  12:  

  13: var mypath =  serverUri +'?DialogId=%7b' +dialogId.toUpperCase()  +'%7d&EntityName=' + typeName+'&ObjectId=%7b' +recordId+'%7d';

  14:  

  15: // First item from selected contacts only

  16: window.showModalDialog(mypath);

  17:  

  18: // Reload form.

  19: window.location.reload(true);

  20: }

In above JavaScript method I have used 3 different parameters.

dialogId is id of the dialog we need to open from button (String Parameter)
typeName is entity’s logical name (String Parameter)
recordId is entity’s current record id form where the dialog will be launched.

3.  Get the Dialog Id Value
· Once you have created new dialog.
· Click Start Dialog.



· That will open new dialog window.
· Get the dialog id value from the URL(Ctrl+N) and note it down somewhere,
   to use the same in coming steps.



4. Insert custom button on entity’s form using Visual Ribbon editor. ( To create a new button on ribbon, you can refer below link---)

http://ankit.inkeysolutions.com/2012/01/crm-2011-how-to-use-visual-ribbon.html

5. After inserting a new custom button,
follow the below steps to add JavaScript function with parameters
· Click on Add link for specifying the “Function name” and “Library”
           a. Specify function name and library name.
          b. Click on “Add” link as shown below. It will open a list of
              parameter types which you can pass with the function.



As discussed earlier (refer step 2) we need 3 different parameters to call JavaScript method. Pass those values as described below. The first parameter will be the value which we have noted in step 3. The second one is entity name. And the last one is the record GUID.



· Clicks on save. It will save and publish the changes on CRM server.



6. Now you can verify the button on your entity’s form ribbon.



7. On click of this button your dialog will be launched.



8. Select Flight Name from the list and click next and then Finish on the last screen.



9. At the end, a note will be generated on Flight Route entity