tag:blogger.com,1999:blog-47319979397543100452024-02-06T20:47:49.978-08:00All around Information TechnologyIn this blog you can find:
Useful and simple code snippets in the area of Dynamics CRM (T-SQL, PowerShell).
Interesting topics about (TIBCO) Scribe Insight.Unknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-4731997939754310045.post-7548545048458220752020-07-14T22:00:00.000-07:002020-07-15T00:35:30.803-07:00MSMQ - Add user privileges to queues via PowerShell<font face="helvetica">In PowerShell, the <i>Set-MsmqQueueACL</i> cmdlet can be used to modify the access rights of queues.</font><div><font face="helvetica"><br /></font></div><div><font face="helvetica"><b>Sample - Add "FullControl" permission to user for all queues of type "Private":</b></font></div><div><font face="helvetica"><b><br /></b></font></div><div><font face="courier">Get-MsmqQueue -QueueType Private | Set-MsmqQueueAcl -UserName "domain\username" -Allow FullControl</font><font face="helvetica"><br /></font><div><br /></div><div><br /></div><font face="helvetica">Reference: <a href="https://docs.microsoft.com/en-us/powershell/module/msmq/set-msmqqueueacl" target="_blank">https://docs.microsoft.com/en-us/powershell/module/msmq/set-msmqqueueacl</a></font><div><br /></div><div><br /></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4731997939754310045.post-27110134333138252832020-07-12T01:40:00.001-07:002020-07-15T00:36:24.057-07:00TIBCO Scribe Insight installation does not start (Windows Installer)<h2 style="text-align: left;"><font face="helvetica">Issue Description - Steps to reproduce:</font></h2><div><font face="helvetica">Operation System:Windows Server 2012 R2</font></div><div><ul style="text-align: left;"><li><font face="helvetica">Fulfil all requirements (Install Windows Installer, .NET Framework, etc.)</font></li></ul><ul style="text-align: left;"><li><font face="helvetica">Click "Start Installation" in Setup</font></li></ul></div><div class="separator" style="clear: both; text-align: center;"><font face="helvetica"><br /></font></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga6zE6T7HxB2NMPXWH4l5yjw2tm1hcDrMzoL5V03Y2sZ4AjNcJkRbxZ-RZDK4mAHx2MI02u9zkGgXk7Vw-PBIxUc-Qu20AqnXPrd5gUCRXVtxL2_jXHml8Sky7nAfNd8XXqRqOHLiK3jBj/s795/scribe_installer1.png" style="margin-left: 1em; margin-right: 1em;"><font face="helvetica"><img border="0" data-original-height="779" data-original-width="795" height="393" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga6zE6T7HxB2NMPXWH4l5yjw2tm1hcDrMzoL5V03Y2sZ4AjNcJkRbxZ-RZDK4mAHx2MI02u9zkGgXk7Vw-PBIxUc-Qu20AqnXPrd5gUCRXVtxL2_jXHml8Sky7nAfNd8XXqRqOHLiK3jBj/w400-h393/scribe_installer1.png" width="400" /></font></a></div><div><font face="helvetica"><br /></font></div><div><ul style="text-align: left;"><li><font face="helvetica">Now, it ends with an open "Windows Installer" window, which contains the parameter description for "msiexec.exe".The installation does not start.</font></li></ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheGSRspefVh-LtpF9tsq6o-9ckXAFL8XsLcZInG4aWTEPI4H_ZXY3ItmnEtdiR4refjExYcPqIEOILQYY3XBGszbARAvfT-7L1fTNb5sduIgDtraqmK6GJ70ExPa0z9EB2hUn5PUC2bdxy/s795/scribe_installer.png" style="margin-left: 1em; margin-right: 1em;"><font face="helvetica"><img border="0" data-original-height="779" data-original-width="795" height="393" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheGSRspefVh-LtpF9tsq6o-9ckXAFL8XsLcZInG4aWTEPI4H_ZXY3ItmnEtdiR4refjExYcPqIEOILQYY3XBGszbARAvfT-7L1fTNb5sduIgDtraqmK6GJ70ExPa0z9EB2hUn5PUC2bdxy/w400-h393/scribe_installer.png" width="400" /></font></a></div><div><font face="helvetica"><br /></font></div></div><div><h1 style="text-align: left;"><font face="helvetica">Resolution:</font></h1><div><font face="helvetica">The root cause is the length of the path to the "ScribeInsight.msi" file in the "Setup" folder.</font></div><div><font face="helvetica"><br /></font></div><div><font face="helvetica">To resolve the issue you can rename (shorten) the folder names or move the installation folder and start the "Setup.exe" again.</font></div><div class="separator" style="clear: both; text-align: center;"><br /></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4731997939754310045.post-44336524876895291482020-07-11T00:01:00.002-07:002020-07-15T00:37:12.432-07:00Get Option Set values directly from the CRM database<pre style="text-align: left;"><font face="courier">USE CRM_MSCRM
GO</font></pre>
<pre style="text-align: left;"><font face="courier">SELECT
sm.AttributeValue
,sm.Value
FROM StringMap sm with(nolock)
inner join EntityLogicalView el with(nolock) on</font></pre>
<pre style="text-align: left;"><font face="courier"> sm.ObjectTypeCode = el.ObjectTypeCode</font></pre>
<pre style="text-align: left;"><font face="courier">WHERE
AttributeName = 'statuscode'
AND LangId = 1033
AND el.Name = 'Opportunity'</font></pre>
<pre style="text-align: left;"><pre style="text-align: left;"><pre><font face="courier">GO</font></pre></pre></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivpO2eC32u6DjKWTPg-7hbREATuQt62waZ4icYeVYdO9tfKLDUP_DrWnQk0q_SMKvbBG4E6uis5RKiKLoFUGKyKyfm6ker9jsf1YD-1b_jC07ZoP5Z_2fLNipO7huNpTzNoDpNXiMJJ7kY/s665/Get+Option+Set+values+directly+from+database.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="325" data-original-width="665" height="244" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivpO2eC32u6DjKWTPg-7hbREATuQt62waZ4icYeVYdO9tfKLDUP_DrWnQk0q_SMKvbBG4E6uis5RKiKLoFUGKyKyfm6ker9jsf1YD-1b_jC07ZoP5Z_2fLNipO7huNpTzNoDpNXiMJJ7kY/w500-h244/Get+Option+Set+values+directly+from+database.png" width="500" /></a></div>
<pre style="text-align: left;"></pre>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4731997939754310045.post-3685647788099926532020-07-10T03:23:00.002-07:002020-07-15T00:38:14.826-07:00How to figure out which columns contain data <font face="helvetica">Imagine you want to know, which columns of a database table contain data and which are empty. <br />
To go through each column to check if any row contains data is a time-consuming task. <br />
<br />
</font><div><font face="helvetica">
This script could help:</font></div>
<div>
<pre><font face="courier" size="2">/*--------------------------------------------------------------------------------*/</font></pre>
</div>
<div style="line-height: 1;">
<pre style="line-height: 1;"><font face="courier" size="2">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 + ''',</font></pre>
<pre style="line-height: 1;"><font face="courier" size="2"><span style="line-height: 1;"> </span><span style="line-height: 1;"> </span>MAX(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 0 ELSE 1 END) AS IsFilled</font></pre>
<pre style="line-height: 1;"><font face="courier" size="2"><span style="line-height: 1;"> </span><span style="line-height: 1;"> </span>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) + ']]></font></pre>
<pre style="line-height: 1;"><font face="courier" size="2"> </XML>') AS [xmlWrappedSQLCommand];</font></pre>
<pre style="text-align: left;"><pre><font face="courier" size="2">/*--------------------------------------------------------------------------------*/</font></pre>
</pre>
<div style="text-align: left;"><font face="helvetica">
The result is a executable script. Just copy it into a new query window to execute it.</font></div>
<div style="text-align: left;">
<font face="helvetica"><br /></font></div>
<div style="text-align: center;"><font face="helvetica">
Result (XML):</font></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq98_aRXjK8KOK6SCrzqF4DwJjmHDRJ3qWXSWeGl25rkn2rxB12BFuy5zLeiMNkN23cEiTWdNQlVl-B_C95RDOPae5QaGNpT9ian7OpxC0Ex8xg4lkQ62ay31WKXgqA8jJGQq5yXmQWQmC/s879/contains-data.png" style="margin-left: 1em; margin-right: 1em;"><font face="helvetica"><img border="0" data-original-height="88" data-original-width="879" height="63" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq98_aRXjK8KOK6SCrzqF4DwJjmHDRJ3qWXSWeGl25rkn2rxB12BFuy5zLeiMNkN23cEiTWdNQlVl-B_C95RDOPae5QaGNpT9ian7OpxC0Ex8xg4lkQ62ay31WKXgqA8jJGQq5yXmQWQmC/w625-h63/contains-data.png" width="625" /></font></a></div>
<div class="separator" style="clear: both; text-align: center;">
<font face="helvetica"><br /></font></div>
<div class="separator" style="clear: both; text-align: center;"><font face="helvetica">
Script Execution Result:</font></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnXrnpJMaxDIihJ9t3gz544xwgUirPSK999_BE3wdE8eLt83YQHT2fRn3WfJX26FQB1wGk5SDrOKFaadH-s-5jidbyiBa6gHOcLf60USAziZeFj3X68rxbTZVFJZr-6qq_SacHucHwD3xl/s844/contains-data_2.png" style="margin-left: 1em; margin-right: 1em;"><font face="helvetica"><img border="0" data-original-height="228" data-original-width="844" height="169" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnXrnpJMaxDIihJ9t3gz544xwgUirPSK999_BE3wdE8eLt83YQHT2fRn3WfJX26FQB1wGk5SDrOKFaadH-s-5jidbyiBa6gHOcLf60USAziZeFj3X68rxbTZVFJZr-6qq_SacHucHwD3xl/w625-h169/contains-data_2.png" width="625" /></font></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div style="text-align: center;">
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4731997939754310045.post-90934647935644596492020-07-10T02:50:00.003-07:002020-07-15T00:39:01.291-07:00Retrieve Subsidiary Businessunits (hierarchy) via T-SQL from CRM Database<pre style="text-align: left;"><font face="courier" size="2">USE CRM_MSCRM
GO</font></pre>
<pre style="text-align: left;"><font face="courier" size="2">DECLARE @rootName NVARCHAR(255) = 'CRM' /* Parent Business Unit Name */
</font></pre>
<pre style="text-align: left;"><font face="courier" size="2">DECLARE @SubsidiaryBusinesseUnits TABLE</font></pre>
<pre style="text-align: left;"><font face="courier" size="2"> (businessunitid UNIQUEIDENTIFIER PRIMARY KEY, depth INT)
DECLARE @depth INT = 0</font></pre>
<pre style="text-align: left;"><font face="courier" size="2">INSERT @SubsidiaryBusinesseUnits
<span style="white-space: pre;"> </span>SELECT businessunitid ,@depth FROM BusinessUnitBase WHERE Name = @rootName
WHILE (@@rowcount > 0)
BEGIN
<span style="white-space: pre;"> </span>SELECT @depth = @depth + 1
<span style="white-space: pre;"> </span>INSERT @SubsidiaryBusinesseUnits
<span style="white-space: pre;"> </span>SELECT t.BusinessUnitId
<span style="white-space: pre;"> </span>,@depth
<span style="white-space: pre;"> </span>FROM BusinessUnitBase t
<span style="white-space: pre;"> </span>WHERE EXISTS (
<span style="white-space: pre;"> </span>SELECT *
<span style="white-space: pre;"> </span>FROM @SubsidiaryBusinesseUnits s
<span style="white-space: pre;"> </span>WHERE s.depth = (@depth - 1)
<span style="white-space: pre;"> </span>AND s.businessunitid = t.ParentBusinessUnitId
<span style="white-space: pre;"> </span>)
END
SELECT * FROM @SubsidiaryBusinesseUnits</font></pre><pre style="text-align: left;"><font face="courier" size="2"><br /></font></pre>
<pre style="text-align: left;"></pre>
<div style="text-align: center;"><font face="helvetica">
SAMPLE: CRM Business Unit Structure</font></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhp89BwsVTzUZcuvRO0BCE9sYOpEExBQ4VUlNIas1E02-alqBGg4X5HWMn8Oj_yRQoV916U_dhMNiWna7JeEncAoY9tzj8b2HGQz2ki8InB_074SHwPTk18TacvAGnmkqjjZ9qbJKTP0yhk/s641/CRM+BU+Hierarchy.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="174" data-original-width="641" height="136" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhp89BwsVTzUZcuvRO0BCE9sYOpEExBQ4VUlNIas1E02-alqBGg4X5HWMn8Oj_yRQoV916U_dhMNiWna7JeEncAoY9tzj8b2HGQz2ki8InB_074SHwPTk18TacvAGnmkqjjZ9qbJKTP0yhk/w500-h136/CRM+BU+Hierarchy.png" title="CRM Businessunit structure" width="500" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div style="text-align: center;"><font face="helvetica">
SQL Result</font></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhljEP8OucK_CkFnSfNiQ_mCzUU-kwAPnpXirhvp3eu0pSzeVnXayWM99iHiVjBqW_ZFuoXr6tO3T4q7j_s4VoxlT4mhMocdCFhwWNJzEhU1nCJgGT_eFj-E0YVSkb1RsiWrYhhz52xJ41P/s716/CRM+BU+Hierarchy+SQL.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="650" data-original-width="716" height="455" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhljEP8OucK_CkFnSfNiQ_mCzUU-kwAPnpXirhvp3eu0pSzeVnXayWM99iHiVjBqW_ZFuoXr6tO3T4q7j_s4VoxlT4mhMocdCFhwWNJzEhU1nCJgGT_eFj-E0YVSkb1RsiWrYhhz52xJ41P/w500-h455/CRM+BU+Hierarchy+SQL.png" title="SQL Script" width="500" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<pre style="text-align: left;"></pre>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4731997939754310045.post-33052788365933420952020-07-09T15:00:00.001-07:002020-07-15T00:39:22.845-07:00Get all StateCode/StatusCode combinations directly from CRM Database<pre style="text-align: left;"><font face="courier" size="2">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</font></pre><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIuph7SWHZuRARVEaX2C-ZbzVE-gM574SyzXlT54PqL0riNktK6g3wXGOXLziN_oFi3rKrfZKi5-8FLWbqsfAzN_x0kMHbahKn0V8fqH2adCWyg7f40oLm3f8C9IzGxEBzI6TEQR__H6sr/s679/Get+all+StateCode_StatusCode+combinations+directly+from+CRM+Database.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="679" data-original-width="407" height="625" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIuph7SWHZuRARVEaX2C-ZbzVE-gM574SyzXlT54PqL0riNktK6g3wXGOXLziN_oFi3rKrfZKi5-8FLWbqsfAzN_x0kMHbahKn0V8fqH2adCWyg7f40oLm3f8C9IzGxEBzI6TEQR__H6sr/w375-h625/Get+all+StateCode_StatusCode+combinations+directly+from+CRM+Database.png" width="375" /></a></div><pre style="text-align: left;"><br /></pre>Unknownnoreply@blogger.com0