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

 

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..!

Error adding a Custom SRS Report (.rdl file)

We have been making reports on a datawarehouse for a customer.

Uploading these new reports kept generating errors like these: Look for the data sources section in the XML

 

[2009-07-01 12:51:51.7] Process: w3wp |Organization:7d795b17-dd71-dd11-a3c0-00163e0c8691 |Thread: 5 |Category: Application |User: 00000000-0000-0000-0000-000000000000 |Level: Error | ErrorInformation.LogError

>MSCRM Error Report:

——————————————————————————————————–

Error: Exception has been thrown by the target of an invocation.

Error Message: Exception has been thrown by the target of an invocation.

Source File: Not available

Line Number: Not available

Request URL: http://crm.website.com/organization/crmreports/reportproperty.aspx

Stack Trace Info: [NullReferenceException: Object reference not set to an instance of an object.]

at Microsoft.Crm.Reporting.SRSReport.convertDataSource()

at Microsoft.Crm.Reporting.SRSReport..ctor(String xmlContent, String originalFilter, Boolean convertReportToCrm, ExecutionContext context)

at Microsoft.Crm.ObjectModel.ReportService.CreateInternal(IBusinessEntity entity, Boolean isScheduledReport, ExecutionContext context)

at Microsoft.Crm.ObjectModel.ReportService.Create(IBusinessEntity entity, ExecutionContext context)

 

The whole point of MS CRM 4.0 generating the errors in this case was the fact that a different data source was used for the new reports.

All we had to do is:

  • Open the report in code view
  • <DataSources>

    <DataSource Name=”Organisation_MSCRM”>

    <rd:DataSourceID>e4625bd3-c843-4b26-9911-a052ae5daf2c</rd:DataSourceID>

    <DataSourceReference>Organization_MSCRM</DataSourceReference>

    </DataSource>

</DataSources>

  • The data source name has to be the data source all MS CRM 4.0 reports use, when you use a different one.. this error will occur.
  • Replace in all datasets the correct data source name with the data source of all the MS CRM 4.0 reports. This will render the report not functional, but MS CRM 4.0 will accept it.
  • When MS CRM 4.0 had accepted the fawlty report, go to the report server website and correct the data source in the report properties.

 

That’s all there’s to it.

Good luck creating all the reports you want, using whatever data source you need.