Query all CRM databases for empty Transaction Currency fields

 

Sometimes it’s really handy to loop all CRM DBs in search of the value of some field for all organizations. This query results in all DBs + Tables + Columns + Number of empty Transaction Currency Ids

Use this at will for any analysis.

 

SET NOCOUNT ON
GO
use master
GO

DECLARE
@dbsql nvarchar(max),
@dbname nvarchar(500),
@dbid uniqueidentifier,
@variable2 int,
@variable3 int,
@totaldb int = 0,
@totaldbonserver int = 0,
@totaldbwithmatches int = 0

-- Get CRM databases
DECLARE DBCursor CURSOR for SELECT DatabaseName, Id FROM MSCRM_CONFIG.dbo.Organization

OPEN DBCursor
fetch next from DBCursor into @dbname, @dbid

while (@@FETCH_STATUS <> -1)
    BEGIN       
        DECLARE @ParmDefinition NVARCHAR(500)
        DECLARE @columncountquery nvarchar(500) = 'select @variable2OUT = count(*) from dorcastest_mscrm.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = ''TransactionCurrencyId'''

        SET @ParmDefinition = N'@variable2OUT int OUTPUT'
        set @totaldbonserver = @totaldbonserver + 1

        EXEC sp_executesql @columncountquery, @ParmDefinition, @variable2 OUTPUT
            print 'Number of TransactionCurrencyId-columns in ' + @dbname + ': ' + CONVERT(varchar, @variable2)

       if @variable2 > 0
        BEGIN
        declare
                  @columncursor     as cursor,
                  @columnsql        as nvarchar(max),
                  @columnquery      as nvarchar(max),
                  @table_catalog    as nvarchar(255),
                  @table_schema     as nvarchar(255),
                  @table_name       as nvarchar(255),
                  @column_name      as nvarchar(255),
                  @columncount      as int = 0

                  set @columnquery = 'select ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
                  from ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS, ' + @dbname +  '.INFORMATION_SCHEMA.TABLES
                  where ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_catalog = ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_catalog
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_schema= ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_schema
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_name = ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_name
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = ''TransactionCurrencyId''
                  and ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.TABLE_TYPE = ''BASE TABLE'''

                  set @columnsql = 'set @cursor = cursor forward_only static for ' + @columnquery + ' open @cursor;'

                  exec sys.sp_executesql
                        @columnsql
                        ,N'@cursor cursor output'
                        ,@columncursor output

                  fetch next from @columncursor into @table_catalog, @table_schema, @table_name, @column_name
                  while (@@fetch_status = 0)
                  begin
                      declare @currencyidsql nvarchar(max) = 'select @variable3OUT = count(*) from '+ @table_catalog + '.' + @table_schema + '.' + @table_name + ' where transactioncurrencyid is null'
                      declare @Parm3Definition NVARCHAR(500) = N'@variable3OUT int OUTPUT'

                    EXEC sp_executesql @currencyidsql, @Parm3Definition, @variable3 OUTPUT
                    if @variable3 > 0
                              begin      
                                   print  @table_catalog + '.' + @table_schema + '.' + @table_name + '.' + @column_name + ' : ' + CONVERT(varchar, @variable3)
                              end

                        fetch next from @columncursor into @table_catalog, @table_schema, @table_name, @column_name
                  end

                  close @columncursor
                  deallocate @columncursor
        END   

        fetch next from DBCursor into @dbname, @dbid

    END

    /*
    PRINT 'Total CRM databases on server: '
    PRINT @totaldbonserver
    PRINT 'Total CRM databases tested () : '
    Print @totaldb
    PRINT 'Total CRM databases with matches: '
    Print @totaldbwithmatches
    */

CLOSE DBCursor
DEALLOCATE DBCursor

 

Skip the annoying MS CRM 2013/2015 splash screen

Obviously solution this doesn’t apply to CRM Online.

1. Start Registry Editor.

2. Locate registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM.

3. Right-click MSCRM, point to New, click DWORD (32-bit) Value, enter DisableNavTour, and then press ENTER.

4. Right-click DisableNavTour, click Modify.

5. In the Value data box, type 1, and then press ENTER.

6. Close the Registry Editor.

Scramble MS CRM Data

This is the SQL Query for scrambling CRM contact and account data.
Sometimes a copy of customer DB is required.You don’t wanna be hacked… and you most likely won’t be… but just in case… run the queries below..

The Contact-query updates FirstName, MiddleName, LastName and City with a random value from another record.
It looks like real data… but it isn’t.

This method is obviousely unsupported, because it’s direct SQL.
It’s not really fast either … 70.000 records took me 21 hours..

 

–Be sure int match your id column datatype

Declare@iduniqueidentifier

 

–Add a WHERE here to select just a subset of your table

DECLARE ContactCursor CURSORFORSELECTContactIdFROMContact

OPEN ContactCursor

FETCH NEXT FROM ContactCursorINTO@id;

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

–Warning: NEWID() is generated once per query, so update the fullname in two queries.

UPDATEContact

SETFirstName=(SELECTTOP 1 FirstNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETMiddleName=(SELECTTOP 1 MiddleNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETLastName=(SELECTTOP 1 LastNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETAddress1_City=(SELECTTOP 1 Address1_CityFROMContactORDERBYNEWID())

WHERE ContactId = @id

UPDATEContact

SETFullName=RTRIM(FirstName +’ ‘+MiddleName)+’ ‘+ LastName

WHEREContactId=@id

FETCH NEXT FROM ContactCursorINTO@id;

END

 

CLOSE ContactCursor;

DEALLOCATE ContactCursor;

 

 

DECLARE AccountCursor CURSOR FOR

SELECT AccountId FROM Account

OPEN AccountCursor

FETCH NEXT FROM AccountCursor INTO @id;

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @@CURSOR_ROWS;

–Warning: NEWID() is generated once per query, so update the fullname in two queries.

UPDATE Account

SET Name = (SELECT TOP 1 FirstName FROM Contact ORDER BY NEWID())

WHERE AccountId = @id

–UPDATE Contact

— SET Address1_City = (SELECT TOP 1 Address1_City FROM Contact ORDER BY NEWID())

–WHERE ContactId = @id

FETCH NEXT FROM AccountCursor INTO @id;

END

CLOSE AccountCursor;

DEALLOCATE AccountCursor;

Install Windows Identity Foundation on Windows 8.1

 

When trying to install Windows Identity Foundation which I downloaded from
http://www.microsoft.com/en-us/download/details.aspx?id=17331
I got this error.

 

Some say pkgmgr.exe could do the trick… when you unpack the MSU-file.

pkgmgr /n:Windows6.2-KB2693643-x64.xml

This was supported until Windows 8.1 RC the following was supported
In Windows 8.1 pkgmgr.exe is deprecated and DISM.exe is the new kid on the block.

dism.exe /online /enable-feature /featurename=Windows-Identity-Foundation

 

 

Good luck..!

Corrupted Quick Find Views in MS CRM 2011

The Error

I had a MS CRM 2011 solution with a corrupted Quick Find View
The cause of all this is yet unknown… it’s probably something like SolutionPackager XML copy-and-pasting-stuff.
Not a very nice thing.

The View is showing errors…
… the view can be modified… but not saved… causing this error.

“The dependent component [missing Component Type] (Id=[missing Component Id]) does not exist. Failure trying to associate it with [dependent Component Type](Id=[dependent Component Id]) as a dependency. Missing dependency lookup type = PrimaryKeyLookup”

One way of fixing this is via – totally unsupported – database updates.

The Solution

Step #1    Get ObjectTypeCode

select ObjectTypeCode
from MetadataSchema.Entity
where PhysicalName=‘new_abonnement’

This appears to be 10022.

select *
from MetadataSchema.Entity
where ObjectTypeCode = 10022

Stap #2    Get Quick Find View

select *
from SavedQuery
where ReturnedTypeCode = 10022 and QueryType = 4

QueryType 4 is the Quick Find View. Check this for an overview of all QueryTypes.

Stap #3    Check FetchXml

select FetchXml
from SavedQuery
where ReturnedTypeCode = 10022 and QueryType = 4

The result is all wrong… mine looked like this… wrong entity … wrong attributes… all wrong, wrong, wrong

 

<fetch version="1.0" mapping="logical">

    <entity name="hig_donatie">

        <attribute name="hig_name" />

        <attribute name="createdon" />

        <order attribute="hig_name" descending="false" />

        <filter type="and">

            <condition attribute="statecode" operator="eq" value="0" />

        </filter>

        <filter type="or" isquickfindfields="1">

            <condition attribute="importsequencenumber" operator="eq" value="{1}" />

            <condition attribute="transactioncurrencyid" operator="like" value="{0}" />

            <condition attribute="hig_termijnbedrag_base" operator="eq" value="{2}" />

            <condition attribute="hig_termijnbedrag" operator="eq" value="{2}" />

            <condition attribute="hig_statusincassomachtiging" operator="like" value="{0}"/>

            <condition attribute="statecode" operator="like" value="{0}" />

            <condition attribute="hig_soortdonatie" operator="like" value="{0}" />

            <condition attribute="statuscode" operator="like" value="{0}" />

            <condition attribute="overriddencreatedon" operator="on" value="{3}" />

            <condition attribute="hig_opzegreden" operator="like" value="{0}" />

            <condition attribute="hig_opzegdatum" operator="on" value="{3}" />

            <condition attribute="hig_meerderetermijnen" operator="like" value="{0}" />

            <condition attribute="modifiedon" operator="on" value="{3}" />

            <condition attribute="modifiedonbehalfby" operator="like" value="{0}" />

            <condition attribute="modifiedby" operator="like" value="{0}" />

            <condition attribute="createdon" operator="on" value="{3}" />

            <condition attribute="createdonbehalfby" operator="like" value="{0}" />

            <condition attribute="createdby" operator="like" value="{0}" />

            <condition attribute="hig_donatiesid" operator="like" value="{0}" />

            <condition attribute="hig_name" operator="like" value="{0}" />

            <condition attribute="hig_donantiebestemming" operator="like" value="{0}" />

            <condition attribute="hig_datummachtigingontvangen" operator="on" value="{3}"/>

            <condition attribute="hig_contactpersoonid" operator="like" value="{0}" />

            <condition attribute="hig_betaalwijze" operator="like" value="{0}" />

            <condition attribute="hig_betaalmaand" operator="like" value="{0}" />

            <condition attribute="hig_betaalfrequentie" operator="like" value="{0}" />

            <condition attribute="hig_bedrijfid" operator="like" value="{0}" />

            <condition attribute="hig_bedrageenmalig_base" operator="eq" value="{2}" />

            <condition attribute="hig_bedrageenmalig" operator="eq" value="{2}" />

            <condition attribute="hig_bedrag_base" operator="eq" value="{2}" />

            <condition attribute="hig_bedrag" operator="eq" value="{2}" />

            <condition attribute="hig_bankrekeningnummer" operator="like" value="{0}" />

            <condition attribute="hig_automatischeincassogewenst" operator="like" value="{0}" />

            <condition attribute="hig_actie" operator="like" value="{0}" />

            <condition attribute="hig_aantaltermijnen" operator="eq" value="{1}" />

        </filter>

        <attribute name="exchangerate" />

        <attribute name="transactioncurrencyid" />

        <attribute name="hig_termijnbedrag_base" />

        <attribute name="hig_termijnbedrag" />

        <attribute name="hig_statusincassomachtiging" />

        <attribute name="statecode" />

        <attribute name="hig_soortdonatie" />

        <attribute name="statuscode" />

        <attribute name="overriddencreatedon" />

        <attribute name="hig_opzegreden" />

        <attribute name="hig_opzegdatum" />

        <attribute name="hig_meerderetermijnen" />

        <attribute name="modifiedon" />

        <attribute name="modifiedonbehalfby" />

        <attribute name="modifiedby" />

        <attribute name="createdonbehalfby" />

        <attribute name="createdby" />

        <attribute name="hig_donatiesid" />

        <attribute name="hig_donantiebestemming" />

        <attribute name="hig_datummachtigingontvangen" />

        <attribute name="hig_contactpersoonid" />

        <attribute name="hig_betaalwijze" />

        <attribute name="hig_betaalmaand" />

        <attribute name="hig_betaalfrequentie" />

        <attribute name="hig_bedrijfid" />

        <attribute name="hig_bedrageenmalig_base" />

        <attribute name="hig_bedrageenmalig" />

        <attribute name="hig_bedrag_base" />

        <attribute name="hig_bedrag" />

        <attribute name="hig_bankrekeningnummer" />

        <attribute name="hig_automatischeincassogewenst" />

        <attribute name="hig_actie" />

        <attribute name="hig_aantaltermijnen" />

        <attribute name="hig_donatieid" />

    </entity>

</fetch>

 


Stap #4    Correct the FetchXml

update SavedQuery set FetchXml =
‘<fetch version=”1.0″ mapping=”logical”>

    <entity name=”new_abonnement”>

        <attribute name=”new_naam” />

        <filter type=”and”>

            <condition attribute=”statecode” operator=”eq” value=”0″ />

        </filter>

        <filter type=”or” isquickfindfields=”1″>

            <condition attribute=”new_naam” operator=”eq” value=”{1}” />

        </filter>

    </entity>

</fetch>’

where ReturnedTypeCode = 10022 and QueryType = 4

Stap #5    Check the LayoutXml

select LayoutXml

from SavedQuery

where ReturnedTypeCode = 10022 and QueryType = 4

The result is all wrong… mine looked like this… correct entity … wrong attributes… all wrong, wrong, wrong

<grid name=“resultset” object=“10022” jump=“new_name” select=“1” icon=“1” preview=“1”>

    <row name=“result” id=“new_abonnementid”>

        <cell name=“new_name” width=“300” />

        <cell name=“createdon” width=“125” />

        <cell name=“new_aantaltermijnen” width=“100” />

        <cell name=“new_actie” width=“100” />

        <cell name=“new_automatischeincassogewenst” width=“100” />

        <cell name=“new_bankrekeningnummer” width=“100” />

        <cell name=“new_bedrag” width=“100” />

        <cell name=“new_bedrag_base” width=“100” />

        <cell name=“new_bedrageenmalig” width=“100” />

        <cell name=“new_bedrageenmalig_base” width=“100” />

        <cell name=“new_bedrijfid” width=“100” />

        <cell name=“new_betaalfrequentie” width=“100” />

        <cell name=“new_betaalmaand” width=“100” />

        <cell name=“new_betaalwijze” width=“100” />

        <cell name=“new_contactpersoonid” width=“100” />

        <cell name=“new_datummachtigingontvangen” width=“100” />

        <cell name=“new_donantiebestemming” width=“100” />

        <cell name=“new_donatiesid” width=“100” />

        <cell name=“createdby” width=“100” />

        <cell name=“createdonbehalfby” width=“100” />

        <cell name=“modifiedby” width=“100” />

        <cell name=“modifiedonbehalfby” width=“100” />

        <cell name=“modifiedon” width=“100” />

        <cell name=“new_meerderetermijnen” width=“100” />

        <cell name=“new_opzegdatum” width=“100” />

        <cell name=“new_opzegreden” width=“100” />

        <cell name=“overriddencreatedon” width=“100” />

        <cell name=“statuscode” width=“100” />

        <cell name=“new_soortdonatie” width=“100” />

        <cell name=“statecode” width=“100” />

        <cell name=“new_statusincassomachtiging” width=“100” />

        <cell name=“new_termijnbedrag” width=“100” />

        <cell name=“new_termijnbedrag_base” width=“100” />

        <cell name=“transactioncurrencyid” width=“100” />

        <cell name=“exchangerate” width=“100” />

    </row>

</grid>

Stap #5    Correct the LayoutXml

update SavedQuery set LayoutXml =
‘<grid name=”resultset” object=”10022″ jump=”new_naam” select=”1″ icon=”1″ preview=”1″>

    <row name=”result” id=”new_abonnementid”>

        <cell name=”new_naam” width=”300″ />

    </row>

</grid>’

where ReturnedTypeCode = 10022 and QueryType = 4

Tuning MS CRM 2011 Performance

 

Voor Performance verbeteringen op SQL heb ik de afgelopen dagen de volgende resources gebruikt

Voor onze klanten die SQL2012 draaien zijn de volgende Performance Dashboard Report een uitkomst.
Features zoals Missing Indexes zijn een uitkomst… hiervoor is dus geen SQL trace en SQL optimizer meer nodig… zo lijkt het.

Microsoft® SQL Server® 2012 Performance Dashboard Reports
http://www.microsoft.com/en-us/download/details.aspx?id=29063
http://www.mssqltips.com/sqlservertip/2670/install-sql-server-2012-performance-dashboard-reports/

Verder levert een overvollen AsyncOperationBase ook performance problemen, zie hiervoor het volgende artikel.
Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM
http://support.microsoft.com/kb/968520

 

Dan is er nog de kwestie van de PrincipalObjectAccess tabel, die ook kan groeien tot meer dan één miljoen.
Hiervoor is ook een MS supported oplossing beschikbaar.
How to control PrincipalObjectAccess table growth in Microsoft Dynamics CRM 2011
http://support.microsoft.com/kb/2664150

 

En… de http://CRMJOBEDITER.CODEPLEX.COM is onmisbaar voor het correct schedulen van de ReindexAll, Deletion Service etc..

Dus..!

MS CRM 2011 Outlook Client x64 on Windows8

With many thanks to Merlin Schwaiger – Senior CRM Consultant at PowerObjects for writing this post on how to support MS CRM 2011 Outlook Client on Windows and Outlook 2013.

http://crmwizard.blogspot.nl/2011_02_01_archive.html

Long story shortened:

  • The x64 installer for the CRM 2011 Outlook client does not include everything necessary for the client to work.
  • You must download the x64 installer for SQL Compact 3.5.
  • You must upgrade the x64 version of SQL Compact 3.5 to version 3.5.8082.0 with the hotfix.
  • At this point the Outlook client should be able to connect to your CRM 2011 organization.