MSMQ - Add user privileges to queues via PowerShell

In PowerShell, the Set-MsmqQueueACL cmdlet can be used to modify the access rights of queues.

Sample - Add "FullControl" permission to user for all queues of type "Private":

Get-MsmqQueue -QueueType Private | Set-MsmqQueueAcl -UserName "domain\username" -Allow FullControl

Reference: https://docs.microsoft.com/en-us/powershell/module/msmq/set-msmqqueueacl

TIBCO Scribe Insight installation does not start (Windows Installer)

Issue Description - Steps to reproduce:

Operation System:Windows Server 2012 R2

• Fulfil all requirements (Install Windows Installer, .NET Framework, etc.)

• Click "Start Installation" in Setup

• Now, it ends with an open "Windows Installer" window, which contains the parameter description for "msiexec.exe".The installation does not start.

Resolution:

The root cause is the length of the path to the "ScribeInsight.msi" file in the "Setup" folder.

To resolve the issue you can rename (shorten) the folder names or move the installation folder and start the "Setup.exe" again.

Get Option Set values directly from the CRM database

USE CRM_MSCRM
GO

SELECT
    sm.AttributeValue
    ,sm.Value
FROM StringMap sm with(nolock)
    inner join EntityLogicalView el with(nolock) on

        sm.ObjectTypeCode = el.ObjectTypeCode

WHERE
    AttributeName = 'statuscode'
    AND LangId = 1033
    AND el.Name = 'Opportunity'

GO

How to figure out which columns contain data

Imagine you want to know, which columns of a database table contain data and which are empty.
To go through each column to check if any row contains data is a time-consuming task.

This script could help:

/*--------------------------------------------------------------------------------*/

DECLARE @tablename VARCHAR(50) = 'AccountBase'
 ,@schemaname VARCHAR(20) = 'dbo'
DECLARE @sqlstatement VARCHAR(max)
 ,@i INT
 ,@rowcount INT
DECLARE @temp TABLE (
 sqlstatement NVARCHAR(max)
 ,id INT
 )

INSERT INTO @temp
SELECT 'SELECT COLUMN_NAME = ''' + COLUMN_NAME + ''',

            MAX(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 0 ELSE 1 END) AS IsFilled

            FROM [' + @schemaname + '].' + @tablename
     ,ROW_NUMBER() OVER (ORDER BY cols.COLUMN_NAME) AS rowno
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = @tablename

SELECT @rowcount = COUNT(*)
 ,@i = 1
FROM @temp

SELECT @sqlstatement = 'select * from ('

WHILE @rowcount >= @i
BEGIN
 SELECT @sqlstatement = CASE 
   WHEN @rowcount = @i
    THEN @sqlstatement + CHAR(13) + (
      SELECT sqlstatement
      FROM @temp
      WHERE id = @i
      )
   ELSE @sqlstatement + CHAR(13) + (
     SELECT sqlstatement
     FROM @temp
     WHERE id = @i
     ) + ' UNION'
   END

 SELECT @i = @i + 1
END

SELECT @sqlstatement = @sqlstatement + CHAR(13) + ') temp'

SELECT CONVERT(XML, '<XML><![CDATA[' + CHAR(13) + @sqlstatement + CHAR(13) + ']]>

    </XML>') AS [xmlWrappedSQLCommand];

/*--------------------------------------------------------------------------------*/

The result is a executable script. Just copy it into a new query window to execute it.

Result (XML):

Script Execution Result:

Retrieve Subsidiary Businessunits (hierarchy) via T-SQL from CRM Database

USE CRM_MSCRM
GO

DECLARE @rootName NVARCHAR(255) = 'CRM' /* Parent Business Unit Name */

DECLARE @SubsidiaryBusinesseUnits TABLE

 (businessunitid UNIQUEIDENTIFIER PRIMARY KEY, depth INT)
DECLARE @depth INT = 0

INSERT @SubsidiaryBusinesseUnits
 SELECT businessunitid ,@depth FROM BusinessUnitBase WHERE Name = @rootName
WHILE (@@rowcount > 0)
BEGIN
 SELECT @depth = @depth + 1
 INSERT @SubsidiaryBusinesseUnits
 SELECT t.BusinessUnitId
  ,@depth
 FROM BusinessUnitBase t
 WHERE EXISTS (
   SELECT *
   FROM @SubsidiaryBusinesseUnits s
   WHERE s.depth = (@depth - 1)
    AND s.businessunitid = t.ParentBusinessUnitId
   )
END
SELECT * FROM @SubsidiaryBusinesseUnits

SAMPLE: CRM Business Unit Structure

SQL Result

Get all StateCode/StatusCode combinations directly from CRM Database

USE CRM_MSCRM
GO

declare @entityname nvarchar(50) = 'opportunity'

select
	sm.State as '[statecode]'
	,m_state.Value 'Statecode Name'

	,sm.Status as '[statuscode]'
	,m_status.Value 'Statuscode Name'

from StatusMap sm
	inner join EntityView ev on
		sm.ObjectTypeCode = ev.ObjectTypeCode
	inner join StringMap m_state on
		m_state.ObjectTypeCode = sm.ObjectTypeCode
		and m_state.AttributeName = 'statecode'
		and m_state.LangId = 1033
		and m_state.AttributeValue = sm.State

	inner join StringMap m_status on
		m_status.ObjectTypeCode = sm.ObjectTypeCode
		and m_status.AttributeName = 'statuscode'
		and m_status.LangId = 1033
		and m_status.AttributeValue = sm.Status 
where
	ev.Name = @entityname
order by
	sm.State
	,sm.Status