SQL Top parent record in hierarchy

https://stackoverflow.com/questions/9211173/sql-top-parent-record-in-hierarchy

SQL SERVER : sp_who2 Replacement

SELECT  spid,
        sp.[status],
        loginame [Login],
        hostname,
        blocked BlkBy,
        sd.name DBName,
        cmd Command,
        cpu CPUTime,
        physical_io DiskIO,
        last_batch LastBatch,
        [program_name] ProgramName  
FROM master.dbo.sysprocesses sp (nolock)
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
--WHERE sp.hostname like '%654%'
--ORDER BY spid
ORDER by
login
--cpuTime desc

Fixing AD issues for CRM

select

 crmuserid  --reference to the SystemUserId in CRM
,suo.userid  --reference to the SystemUserAuthentication table

,AuthInfo
,domainname
,fullname

,sua.Id


from

[MSCRM_CONFIG].[dbo].SystemUserOrganizations  suo

LEFT JOIN [MSCRM_CONFIG].[dbo].SystemUserAuthentication sua
ON suo.UserId = sua.UserId


LEFT JOIN Row2015_MSCRM.dbo.systemuser su
ON suo.CrmUserId = su.SystemUserId

where
suo.OrganizationId =
(
select ID from MSCRM_CONFIG.dbo.Organization where UniqueName = 'Row2015'
)


and su.DomainName like '%Xacv622test1%'

--W:S-1-5-21-1659004503-2025429265-725345543-164647
--W:S-1-5-21-1659004503-2025429265-725345543-184821


update [MSCRM_CONFIG].[dbo].SystemUserAuthentication
set AuthInfo = 'W:S-1-5-21-1659004503-2025429265-725345543-184821'

where id = 'A3E25B91-97EB-E511-80D3-005056AC11FA'

$objUser = New-Object System.Security.Principal.NTAccount("DOMAIN", "USERNAME") 
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier]) 
$strSID.Value

http://www.celedonpartners.com/blog/reconnecting-active-directory-accounts-in-dynamics-crm/

Calculate number of records updated per minute

Select
DateAdd(minute, DateDiff(minute, 0, ModifiedOn), 0) AS [DAY_MINUTE], Count(*) AS [TOTAL_MINUTE]
,
Count(*) / 60.0 AS [TOTAL_SECOND]
From row2015_mscrm.dbo.contact (nolock)

where ModifiedOn > DATEADD(mi,(-120)   -15,getdate()) --'2016-08-09 08:40:00.000'

Group By DateAdd(minute, DateDiff(minute, 0, ModifiedOn), 0)


order by 1 desc

Script to retrieve the Maintenance Jobs

select

LastRunTime,

CASE OperationType
       WHEN 14 THEN 'DeletionService'
       WHEN 15 THEN 'IndexManagement'
       WHEN 30 THEN 'ReindexAll'
       WHEN 32 THEN 'CleanupInactiveWorkflowAssemblies'
       WHEN 40 THEN 'GoalRollup'
END OperationType, OperationType

,

enabled, ExecutionTime,LastResultCode,LastResultData
,MaintenanceWindowEndTime,MaintenanceWindowStartTime, ModifiedOn
,NextRunTime,RecurrencePattern, RecurrenceStartTime, StartedOn, State, IsDeleted

 from ScaleGroupOrganizationMaintenanceJobs


 order by LastRunTime desc

The jobs below are run by CRM async Service (Maintenance)

Maintenance Job Name	Purpose	Default Frequency/ Recommendation	Operation Type
Deletion Service	The deletion service maintenance operation now cleans up subscription tracking records for deleted metadata objects as they expire. ·         If Outlook does not sync for 90 days, the job will remove the subscription with CRM. ·         Cleanup POA records. Un-sharing of record(s) do not remove the records in the POA table, it will stay there for 90 days and it will clean up by the deletion job. ·         Cleanup failed/stuck workflows for the record(s) synchronize with Outlook.	By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night	14
Indexing Management	Validates that system-managed indexes exist for all entities and recreates any missing indexes. This is more for CRM Online and only impacts us during configuration of the solution. It automatically creates index for each column used in search for Quick Find if the index is not already created in the CRM database	By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night	15
Organization Full Text Catalog Index	Full Text Catalog Index		25
Reindex All	Reorganizes/rebuilds fragmented indexes depending on the amount of fragmentation. It execute the p_ReindexAll stored procedure, it selects all indexes in the database with greater than 30% fragmentation and runs ReIndex on each while in online mode. Run while online mode means indexes are not taken offline while reindex is happening they still are active and are functioning and can be used. Large enterprise CRM implementation with big data volume should disable this CRM reindex job if a custom job is created for recreating the indexes and with run update stats.	By default, the job executes every 24 hours, disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999	30
Cleanup Inactive Workflow Assemblies	Seeks custom workflow assemblies that are no longer referenced in workflow rules or in-process jobs. Those unreferenced assemblies are then deleted. Consider the scenario where you register version 2.0 of a custom workflow assembly.  You may update your rules to reference the new version, but some in-progress jobs may still be referencing version 1.0.  Once those jobs have completed, this maintenance job will clean up the version 1.0 assembly that is no longer referenced by rules/jobs. It executes the p_CleanupInactiveWorkflowAssembliesstored procedure.	By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night	32
Create Audit Partition	Alters the partitioning scheme for the auditbase table (SQL Enterprise only).	By default, the job executes every month, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Check for MUI Updates	Detects upgrades to language (MUI) packs and schedules additional async operations to perform individual language provisioning.	By default, the job executes every 24 hours, if you don’t have language pack installed, you can disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999
Refresh Entity Row Count	Refreshes the Record Count snapshot statistics leveraged enhanced query plans. This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.	By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Refresh Sharing Count	Refreshes the POA read snapshot statistics leveraged in enhanced query plans. This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.	By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Script to retrieve the Job Activity

SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

Audit Parsing

declare @dt datetime
select @dt = getdate()-4
--select @dt


 IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
 IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
 IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;


 CREATE TABLE #audit(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
attributemask nvarchar(max),
changedata nvarchar(max),
operation int,
changedOn datetime,
changedBy nvarchar(200)
)

 CREATE TABLE #attribute(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
atribute int,
operation int,
changedOn datetime,
changedBy nvarchar(200)

)

 CREATE TABLE #value(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
value nvarchar(max)
)



INSERT INTO #audit(objectid,attributemask,changedata, objectTypeCode, operation, changedOn, changedBy)

select


objectid ,substring(attributemask,2,len(attributemask)-2) AttributeMask,ChangeData, objectTypeCode, Operation, CreatedOn, UserIdName


from Audit a


WHERE
a.operation in  (1,2)
AND a.Action in  (1,2)

AND a.ObjectTypeCode = 2

and a.CreatedOn >= @dt
--and a.useridname = 'sa_CRM_Delta, sa_CRM_Delta'
AND a.ChangeData!=''




declare @rowNum bigint
DECLARE @objectid uniqueidentifier
declare @attributemask nvarchar(max)
declare @changedata nvarchar(max)
DECLARE @auxAtrib xml
DECLARE @auxVals xml
declare @objectTypeCode int

declare @operation int
declare @changedOn datetime
declare @changedBy nvarchar(200)


declare @splitAttrib char(1)
set @splitAttrib = ','

declare @splitVals char(1)
set @splitVals = '~'


DECLARE audit_cursor CURSOR FOR
SELECT id,objectid,attributemask,changedata, objectTypeCode,operation, changedOn, changedBy
FROM #audit

open audit_cursor

FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy


WHILE @@FETCH_STATUS = 0
BEGIN


SELECT @auxAtrib = CONVERT(xml,' <root> <s>' + REPLACE(@attributemask,@splitAttrib,'</s> <s>') + '</s>   </root> ')
SELECT @auxVals = CONVERT(xml,' <root> <s>' + REPLACE(   replace(@changedata,'&','&amp;')     ,@splitVals,'</s> <s>') + '</s>   </root> ')

INSERT INTO #attribute(objectid, objectTypeCode , atribute,operation,changedOn,changedBy)
SELECT @objectid id, @objectTypeCode, atrib = T.c.value('.','varchar(20)') , @operation, @changedOn, @changedBy
FROM @auxAtrib.nodes('/root/s') T(c)

INSERT INTO #value(objectid, objectTypeCode, value)
SELECT @objectid id, @objectTypeCode, val = T.c.value('.','varchar(20)')
FROM @auxVals.nodes('/root/s') T(c)









FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy

END

CLOSE audit_cursor;
DEALLOCATE audit_cursor;



--truncate table StagingDB.dbo.Audit

--INSERT INTO StagingDB.dbo.Audit


SELECT

--v.objectid

--,v.value

--,a.atribute

 CASE a.operation
WHEN 1 then 'Create'
WHEN 2 then 'Update'
 END  operation
,a.changedOn
,cast(a.changedOn as date) changedOn_date
,a.changedBy
,at.Name
,c.hb_ConsumerId
,c.Address1_Country







FROM  #value v
INNER JOIN #attribute a
ON v.id = a.id

INNER JOIN Row2015_MSCRM.dbo.Contact c
ON v.objectid = c.ContactId and c.statecode=0
INNER JOIN (
SELECT DISTINCT ar.name,     ar.ColumnNumber
FROM MetadataSchema.Attribute ar INNER JOIN
MetadataSchema.Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 ) at
ON a.atribute = at.ColumnNumber






 IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
 IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
 IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;