CRM 2011: Change sitemap customization

Recently I worked upon a project where in I required updating the sitemap customization to have altogether new areas to be replaced with the default CRM areas and subareas of sitemap. I found some interesting things while doing such customization changes, so I would like to share the same here in this post.

1.       You can give a title to areas and the subareas, suppose I want to change the title of default Sales area from Sales to “Property Sales”, just add a title attribute as shown below.

<Area Title=” Property Sales” Id="SFA" ResourceId="Area_Sales" Icon="/_imgs/sales_24x24.gif" DescriptionResourceId="Sales_Description">

The same attribute could be added for any of the subareas to have different titles.

2.       Setup the custom entity as subarea link,

<SubArea Title="Property Information" Id="nav_property_information" Entity="new_property" />

As shown above, you can setup the custom entity as link. Only the Id is the required attribute. This will show the default view of an entity on click of the above link.

Note: You can use either URL or Entity while setup a custom entity link, if by mistake you keep both the attributes in any of subarea, your CRM server will crash, and you could not browse anything for that particular organization. Also you cannot revert back as there is no “Settings” area available to be browsed now. So I would suggest you to have backup of your database before you import the updated sitemap customization/solution.

Visit below link to know more about the rest of the attributes of subarea.

http://msdn.microsoft.com/en-us/library/gg328498.aspx  

3.       Use the character entity reference, decimal character reference, and hexadecimal character reference for markup-significant and internationalization characters, as well as the rendering of such characters in your browser.

For example, suppose you want a subarea title as “Sales, Marketing & Services”. Use “&amp;” entity in place of ‘&’ character. So now your title will be “Sales, Marketing &amp; Services”.

Visit below link for more help.

http://htmlhelp.com/reference/html40/entities/special.html

New DATETIME Data Types in SQL Server 2008

Introduction

From the beginning, Microsoft SQL Server DBAs have longed for two different data types, where you could store time and date separately. The long wait is over. In all of the older versions, we had only one data type that could store Datetime data type, and it stored both the value of the date and the value of the time.

Finally, in Microsoft SQL Server 2008, Microsoft is introducing a set of new data types for storing date, time and both date and time together. The new data types store more data as well, which means you can store dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the fraction of 9999999.

There are four DATETIME data types newly introduced in SQL Server 2008. They are

DATE, TIME, DATETIMEOFFSET and DATETIME2.

1) DATE Data Type

The DATE data type stores only the date value.

The DATE data type returns data in the form of year, Month and Day. However, the format in which the date is returned is determined by the current language setting in SQL Server.

The range for the DATE data type is from 0001-01-01 through 9999-12-31.

When you specify a datetime value, SQL Server automatically converts that value into the DATE data type, which means that only the date portion is stored.

Example:

DECLARE @Date_Type DATE

SET @Date_Type = '2011-05-17 05:29:15.1234567'

SELECT @Date_Type

Result:

2011-05-17

2) TIME Data type

The TIME data type stores only the time value.

The TIME data type returns data in the form of hour, minute, second and fractional second.

The fractional part of the seconds can support up to 7 decimal places.

The precision of the TIME data type supports a range of 00:00:00.0000000 through 23:59:59.9999999.

When you specify the TIME datatype in a Transact-SQL statement, you can specify the precision of the stored values by including the appropriate number within parentheses.

For example, to specify a precision of 7, you would specify TIME(7).

For a precision of 5, you would specify TIME(5), and so on.

If you do not specify the precision, 7 are assumed.

Example:

DECLARE @Time_Type TIME

SET @Time_Type = '2011-05-17 05:29:15.1234567'

SELECT @Time_Type

Result:

05:29:15.1234567

You can also specify a different precision when converting the data

DECLARE @DateTimeOffset_Type TIME

DECLARE @Time_Type TIME

SET @DateTimeOffset_Type = '2011-05-17 05:29:15.1234567'

SET @Time_Type = CAST(@DateTimeOffset_Type AS TIME(5))

SELECT @Time_Type

Result:

05:29:15.1234600

Notice that the fractional seconds have been rounded up.

The original fractional seconds were .1234567. However, if you specify a precision of 5, SQL Server will round up the "67" fractional part, returning .1234600 in the results, rather than .1234500.

When inserting data into a TIME column, you can specify a time value or a datetime value as shown in the following INSERT statements:

INSERT INTO TableName (ID, Time_DataType)

VALUES ('1004, 05:23:10.1234567'); --Only Time

INSERT INTO TableName (ID, Time_DataType)

VALUES (1005, '2011-05-17 05:23:10.1234567'); --Date & Time

In both cases, only the time is inserted into the Time_DataType column.

3) DATETIME2 Data Type

The DATETIME2 data type stores the both date and time value.

The data type DATETIME2 is the combination of the data type DATE and TIME.

DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value 00:00:00.0000000 to 23:59:59.9999999.

Example:

DECLARE @DateTime2_Type DATETIME2

SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')

SELECT @DateTime2_Type

Result:

0001-01-01 16:14:00.1234567

We could limit the precision like (Precision value range 0-7),

DECLARE @DateTime2_Type DATETIME2(4)

SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')

SELECT @DateTime2_Type

Result:

0001-01-01 16:14:00.1235

Notice that the fractional seconds have been rounded up.

4) DATETIMEOFFSET Data type

The DATETIMEOFFSET data type stores the date, time and time-zone offset value.

The DATETIMEOFFSET data type is nearly identical to the DATETIME2 data type, except that a DATETIMEOFFSET value includes one important addition that is a time-zone offset value.

The offset value represents the number of hours and minutes before or after Coordinated Universal Time (UTC).

A positive number indicates the amount of time to add to the UTC to determine the local time. (+05:30).

A negative number indicates the amount of time to subtract from the UTC to determine the local time. (-05:30)

The Date range is between 0001-01-01 and 9999-12-31.

The Time Range is between 00:00:00 and 23:59:59.9999999.

The Offset range is between -14:00 through +14:00.

Example:

DECLARE @DateTimeOffset_Type DATETIMEOFFSET

SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')

SELECT @DateTimeOffset_Type

Result:

0001-01-01 16:14:00.1234567 +05:30

You can also set the precision of the data type, like

DECLARE @DateTimeOffset_Type DATETIMEOFFSET(4)

SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')

SELECT @DateTimeOffset_Type

Result:

0001-01-01 16:14:00.1235 +05:30

Summary:

Data type	Format	Range	Accuracy	Storage size (bytes)	User-defined fractional second precision	Time zone offset
TIME	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 to 23:59:59.9999999	100 nanoseconds	3 to 5	Yes	No
DATE	YYYY-MM-DD	00001-01-01 to 9999-12-31	1 day	3	No	No
SMALLDATETIME	YYYY-MM-DD hh:mm:ss	1900-01-01 to 2079-06-06	1 minute	4	No	No
DATETIME	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 to 9999-12-31	0.333 second	8	No	No
DATETIME2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999	100 nanoseconds	6 to 8	Yes	No
DATETIMEOFFSET	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	00001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 (in UTC)	100 nanoseconds	8 to 10	Yes	Yes

Creating Tally Tables using CTE in SQL Server 2005/2008

A Tally table is simply a table with a single column of very well indexed sequential values starting at any value of your requirement and going up to some predefined threshold. The starting value & the ending value should not be arbitrary. It should be as per the requirement in question.

There are n numbers of ways in which a Tally Table could be generated and the most obvious one is by making use of loops.

Here, I would demonstrate how we could generate a Tally Table using CTE. We will make use of the recursive nature of CTEs to get our job done.

Generating a sequence of numbers from 1 to 20

DECLARE @Max AS INT = 20

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE

Generating a sequence of Dates starting with the current date & going till next 20 days

DECLARE @MaxDate AS DATETIME = GETDATE() + 20

;WITH CTE AS (

  SELECT GETDATE() Dates

  UNION ALL

  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate

)

SELECT * FROM CTE

I love this way of generating the Tally Tables as it’s just too simple and on the fly.

Merge Statement in SQL Server 2008

In versions of SQL Server prior to SQL Server 2008, if you had a set of data rows in a source table that you wanted to synchronize with a target table, you had to perform at least three operations:

I. One scan of the source table to find matching rows to update in the target table,

II. Another scan of the source table to find non matching rows to insert into the target table,

III. And a third scan to find rows in the target table not contained in the source table that needed to be deleted.

SQL Server 2008, however, introduces the MERGE statement. With the MERGE statement, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table, all in just a single statement, minimizing the number of times that rows in the source and target tables need to be processed.

The MERGE statement can also be used for performing conditional inserts or updates of rows in a target table from a source table.

The basic syntax of the MERGE statement is as follows:

[ WITH common_table_expression [,...n] ]

MERGE

[ TOP ( N ) [ PERCENT ] ]

[ INTO ] target_table [ [ AS ] table_alias ]

USING table_or_view_name [ [ AS ] table_alias ]

ON merge_search_condition

[ WHEN MATCHED [ AND search_condition ]

THEN { UPDATE SET set_clause | DELETE } ] [ ...n ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND search_condition ]

THEN { INSERT [ ( column_list ) ] { VALUES ( values_list ) | DEFAULT VALUES}} ]

[ WHEN NOT MATCHED BY SOURCE [ AND search_condition ]

THEN { UPDATE SET set_clause | DELETE } ] [ ...n ]

[ OUTPUT column_name | scalar_expression

INTO { @table_variable | output_table } [ (column_list) ] ]

[ OUTPUT column_name | scalar_expression [ [AS] column_alias_identifier ] [,...n ] ] ;

The MERGE syntax consists of the following primary clauses:

1. The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.

2. The USING clause specifies the data source being joined with the target. The ON clause specifies the join conditions that determine how the target and source match.

3. The WHEN MATCHED clause specifies either the update or delete operation to perform when rows of target table match rows in the source table and any additional search conditions.

4. WHEN NOT MATCHED BY TARGET specifies the insert operation when a row in the source table does not have a match in the target table.

5. WHEN NOT MATCHED BY SOURCE specifies the update or delete operation to perform when rows of the target table do not have matches in the source table.

6. The OUTPUT clause returns a row for each row in the target that is inserted, updated or deleted.

The WHEN clauses specify the actions to take on the rows identified by the conditions specified in the ON clause. The conditions specified in the ON clause determine the full result set that will be operated on. Additional filtering to restrict the affected rows can be specified in the WHEN clauses. Multiple WHEN clauses with different search conditions can be specified. However, if there is a MATCH clause that includes a search condition, it must be specified before all other WHEN MATCH clauses.

Example:

SELECT * FROM Test01

SELECT * FROM Test02

MERGE

  dbo.Test01

  AS TargetLV

USING (

        SELECT

          *

        FROM

          dbo.Test02

      ) AS SourceLV

  ON TargetLV.Id = SourceLV.Id

WHEN MATCHED THEN

  UPDATE

  SET

    TargetLV.Name = SourceLV.Name

WHEN NOT MATCHED BY SOURCE THEN

  DELETE

WHEN NOT MATCHED BY TARGET THEN

  INSERT

  (

    ID,

    Name

  )

  VALUES

  (

    SourceLV.ID,

    SourceLV.Name

)

OUTPUT $ACTION, Inserted.Id, Inserted.Name,Deleted.Id, Deleted.Name;

SELECT * FROM Test01

SELECT * FROM Test02

Conclusion

Generally, we can use the MERGE statement while implementing the Master-Child relationship. What I have tried to explain here is just an overview of this statement. For further details, you can refer –

http://msdn.microsoft.com/en-us/library/bb510625.aspx

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/