Skip to main content

Posts

Showing posts from January, 2014

Altering User-Defined Sharing Permissions for Dynamics CRM Charts Directly in the Database

Today I received an email from a user experiencing difficulty editing charts that had been shared with them by another user, who had forgotten to check the "Write" permission. Unfortunately this person was away for the day, and for some reason CRM doesn't have a neat admin tool for managing individual user-specified sharing settings for charts and such.  So I had little choice but to dig into the bowels of the database and set the permissions manually.

User-designed charts are accessible via the UserQueryVisualization view and the actual permission info is stored in PrincipalObjectAccess table. The corresponding record for a specific user/chart can be obtained like this:

SELECT  poa.PrincipalObjectAccessId FROM    UserQueryVisualization uq LEFTJOIN PrincipalObjectAccess poa ON poa.ObjectId = uq.UserQueryVisualizationId LEFTJOIN SystemUserPrincipals sp ON sp.PrincipalId = poa.PrincipalId LEFTJOIN FilteredSystemUser su ON su.systemuserid = sp.SystemUserId WHERE   uq.name ='Char…

TSQL - checking for the intersection of two sets of dates

Double post day!

The same query that had concatenation happening from the previous post also had date range parameters, @StartDate and @EndDate, and needed to only return records (which also had their own start/end date fields) in which the records' were active during the specified date range.  I.e. where the record's date range intersected with the date range parameters.  It confused me for a bit so I'm recording for posterity. As I see it, there are three possible valid cases:

Case 1: Record's start date is within the date range



Case 2: Record's end is within the date range


Case 3: Record's end IS NULL (i.e. still currently active) and record's start is less than the end of the date range



So expressed in code, these three cases looked like this:

SELECT SomeFields FROM  SomeTable t
--case 1 WHERE ((t.Start >= @StartDate AND   t.Start < @EndDate)
--case 2 OR(t.End > @StartDate AND   t.End <= @EndDate)
--case 3 OR(t.End ISNULL AND   t.Start < @EndDate))

TSQL - Removing a trailing comma from a list of concatenated values

On a semi-regular basis I'd need to squish a list of comma-delimited values all concatenated together into one field, so I'd use this old snippet:

SELECTLEFT(ConcatValues,LEN(ConcatValues)-1)AS'TheValues' FROM SomeTable t CROSSAPPLY(SELECT    t2.SomeField +', 'AS [text()] FROM      SomeTable t2 WHERE     t.SomeField = t2.SomeField
FORXMLPATH('')) C(ConcatValues)
Problem is when the concatenated field has length zero, LEFT gets a -1 length and returns an error:

Msg 537, Level 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.
So today I had a look around and found a better way from this StackOverflow thread. Just replace the LEFT function with this:
STUFF(ConcatValues,LEN(ConcatValues), 1,'')
It gives the exact same results except that the STUFF function is way more tolerant of being given inappropriate length parameters and just returns NULL (which is exactly what I want) instead of throwing errors.  From the MSDN docu…