Report Server location and the error: The specified path is not a metabase path

One of the ‘known’ issues with using CRM 4.0 and ESRV0008 is that CRM does not realise that SQL 2008 Reporting Services hosts the ReportServer application outside of IIS. Hence, when you try and edit the ReportServer URL in CRM Deployment Manager, or install CRM, you may get the error "The specified path is not a metabase path". As I said, this is a known issue, and a workaround is described at http://support.microsoft.com/kb/957053 (issue no. 6 in the article).

The problem though is that this workaround doesn’t always, well, work. I tried this, with no success whatsoever. Eventually, I went back to some other issues CRM had had with identifying IIS metadata in the past with CRM 3.0 (e.g. http://support.microsoft.com/kb/916164 ). What I had to do was temporarily change the IIS bindings so that the default web-site (i.e. web-site with ID=1) was on port 80. As long as this is the case when you edit the Report Server URL, then everything works. Fortunately, all I was doing was editing an organisation in an existing deployment, and could arrange downtime to temporarily change web-site bindings; if you’re installing a new CRM deployment it may be harder to play with web-site bindings. For completeness, the steps to make this work were:

1. In IIS Manager, create a new web-site with bindings that match those of the ReportServer URL, as per issue 6 in KB 957053 (link above)
2. In IIS Manager, change the current web-site bindings so that the web-site with ID=1 is on port 80 with no host-headers (if necessary turn off your phone and don’t check email while people try and complain they can’t access their web-sites)
3. In CRM Deployment Manager, disable the organisation, edit the organisation an put in the ReportServer URL you want and continue till the organisation has been updated successfully. Enable the organisation
4. Back in IIS Manager, change the web-site bindings back to how they were, turn your phone back on, and find someone/something else to blame for the temporary outage

Why Microsoft code things assuming the default web-site is on port 80 is beyond me. Oh well.

During my investigations I found out a few more things:

• McAfee anti-virus software hooks into some popular alternate ports (in this case 8081), and it’s not easy to diagnose conflicts when you put ReportServer and McAfee on the same port
• Configuring multiple bindings for the ReportServer URL works fine
• 'All' the above does is make sure that CRM populates the ReportServer with the correct folders, reports, data sources and permissions. It is possible to set all this up manually and get it to work, but it’s very tedious and you may need to change the Guids that identify the reports within either CRM or Report Server

CRM Design Patterns - and back on the MSCRM Team Blog

I seem to be using my invitations to post on the MSCRM Team blog as a prompt to publish posts - I've posted a starter post on Design Patterns.

I'm intending this to be the first in a series of posts on Design Patterns for MS CRM. I'm using the term 'Design Pattern' deliberately to echo the same term used in object-oriented design, though I'm not intending the posts to be particularly formal or prescriptive. If you're interested in the more formal ideas of design patterns, wikipedia as good a place as any to start.

My reasons for investing time in this are two-fold:

1. I don't like having to do thinkgs more than once. When designing MS CRM solutions for a customer probably about 90% of the design decisions I make now are wholly or partly based on a previous, similar design for another customer. A lot of this information is in my head; the intention of building design patterns is to create a structure to be able to communicate this information to others
2. MS CRM solutions are a mix of out-of-the-box functionality, and specific customisations and extensions. This gives a lot of options, and a lot of additional considerations beyond traditional ground-up development, or out-of-box deployment, and I think there's a lot of new and interesting design work that can be done in this environment

Using the IIS Logs to get CRM Usage information

I've been posting on the Microsoft CRM Team blog again.

The article describes how to make use of the IIS logs to get useful information about who is accessing CRM and when.

This technique uses some SQL objects (tables, view and functions), and a reporting services report. The source code for these is available on the MSDN Code Gallery

CRM and SQL 2008 Whitepaper

MS have released an interesting white paper about how you can use some of the new SQL 2008 features with CRM - http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

The row compression feature, along with the sparse columns, look very useful for reducing the number of pages needed by a table, which is important for ad-hoc advanced finds which use able scans, and pre-filtered reports.

Filtered indexes can be very useful, especially on the statecode for relatively volatile entities. For instance, the ability to use filtered indexes on open cases or opportunities could make a big difference to overall index storage

I need to do some more testing before I'm convinced on the benefits of the page compression. I could see it working for large quantities of relatively static data, such as a large account or contact table, but I think the processing overhead on data modification (which is always hard to quantify) means that I'd be reluctant to use it on more volatile data (e.g. opportunities, cases, activities).

Office 2007 System Driver - errors in SSIS if you don't have them

A quick post, as I didn't find any others out there giving all the details you may need. SQL 2008 SSIS supports the Office 2007 file formats, but requires the Office 2007 System Driver.

If you deploy a package onto a machine without these drivers, you get errors like the following when the component is validated:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered
Hresult: 0x80040154 Description: "Class not registered"

Installing the Office 2007 System Driver should fix this.

Some SQL Snippets

Over time (don't ask how long – suffice to say I first used Microsoft SQL Server on OS/2) you pick up a fair amount of useful SQL Server knowledge. This post is intended to be a random collection of snippets that I use and remember, and I expect to add to the post periodically as I encounter further uses for SQL knowledge. So, in no particular order...

Finding SQL objects that contain a particular string
The definition of SQL objects can be accessed via the sys.syscomments view in the SQL database, and can be queried. The following example returns the name of objects that contain 'Test' somewhere within the definition. The object_name function is a quick way to get the name of an object from its id – the other way is to join to the sys.objects view.

select distinct object_name(id) from sys.syscomments where text like '%Test%'

Note that this only works if the SQL object definition was not encrypted with the WITH ENCRYPTION option

Granting Permissions to a set of objects
I've yet to find a good user-interface in SQL for setting permissions on a set of objects quickly, so I tend to use SQL commands. The following procedure shows how to use a cursor to iterate through a set of objects and execute a dynamically-built GRANT statement on them

declare cur cursor fast_forward for

select name from sys.objects
where type = 'V' and name like 'vw_%' -- Get all views, beginning vw_

declare @obj sysname, @sql nvarchar(2000)
open cur
fetch next from cur into @obj
while @@fetch_status = 0
begin
set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'
-- grant select permission to public
exec (@sql)
fetch next from cur into @obj
end
close cur
deallocate cur

Outputting stored procedure information to a table
There are cases when you might want to use the results of a stored procedure in a table structure for future processing. There's not an EXECUTE INTO statement but you can use INSERT ... EXECUTE. You can also use this with dynamically constructed SQL, using EXECUTE (@sql). The following example uses both EXECUTE syntaxes, andshows how to iterate though the names of 'tables' from a linked server – this is used to query Excel spreadsheets where there is a dynamic range of identically structured worksheets

create
table #excelsheets -- Store names of spreadsheets in Excel
( TABLE_CAT sysname null
,TABLE_SCHEM sysname null
,TABLE_NAME sysname not null
,TABLE_TYPE sysname null
,REMARKS nvarchar(255) null )

insert #excelsheets execute sp_tables_ex 'EXCELDYNAMIC'
-- EXCELDYNAMIC is a linked server

create table #tmp
-- Temporary storage of data, so results can be output as one result set
( TABLE_NAME sysname
,[Month] int
,[Target] decimal(10,2) )

declare cur cursor fast_forward
for select TABLE_NAME from #excelsheets
declare @tbl sysname, @sql nvarchar(4000)
open cur
fetch next from cur into @tbl
while @@fetch_status = 0
begin
-- Build dynamic SQL statement. It would be nice to pass the statement as a parameter to OPENQUERY, but that's not permitted
set @sql = 'Select ''' + @tbl + ''' as TABLE_NAME, [Month], [Target] FROM EXCELDYNAMIC...[' + @tbl + ']'
insert #tmp exec (@sql)
fetch next from cur into @tbl
end
-- Cleanup and output results
close cur
deallocate cur
select * from #tmp
drop table #tmp
drop table #excelsheets

Viewing CRM 4.0 Reports in Report Manager

Some of my previous posts (e.g. http://mscrmuk.blogspot.com/2008/01/scheduling-and-emailing-reports-with.html) were written based on CRM 3.0. Most of the reporting principles are similar for CRM 4.0, but the reports are configured slightly differently within Report Manager. The key differences are:

1. The CRM items are hidden by default in Report Manager. If you browse to the OrganisationName_MSCRM folder you won't see the hidden items. To see them, click on the 'Show Details' button on the right of the toolbar
2. The CRM 4.0 reports are stored in a sub-folder called 4.0. Click on this folder to see the reports
3. CRM 4.0 specifies the ReportID Guid as the report name, and the friendly name is now in the Description fields

CRM 4.0 Update Rollup 2

Update Rollup 2 for CRM 4.0 has been released, which is a pretty fast turnaround after the recent Update Rollup 1. Ive not had time to look at many of the fixes yet, but Ill add to this post if anything interesting crops up.

Information about the hotfix: http://support.microsoft.com/default.aspx?kbid=959419
Download link: http://www.microsoft.com/downloads/details.aspx?familyid=aa671769-61e9-45c4-919f-c88199aa4241&displaylang=en&tm

CRM Error Codes

Something that's cropped up on a couple of CRM forums recently is how to get more information about a CRM error code. Error codes can be displayed in one of three formats:

1. Decimal, e.g. -2147206371
2. Hex with the 0x prefix - e.g. 0x80043B1D
3. Hex without the prefix - e.g. 80043B1D

The CRM SDK documentation has a reasonable set of information about each error here, but only refers to error codes in the last of the listed formats (e.g. 80043B1D). To convert the second format to the third format, just remove the 0x prefix.

The easiest way to convert the first (decimal) format is to use the Windows calculator utility (calc.exe), as follows:

1. Open the calculator in scientific mode
2. Check the Decimal (Dec) option is set
3. Paste in the error code - e.g. -2147206371
4. Select 'Hex' to convert it to Hex, this will then look like: FFFFFFFF80043B1D
5. Remove the leading FFFFFFFF. You can then find the resulting code, e.g. 80043B1D, in the error codes

Invalid Argument error when importing unpublished entities

If, like I did recently, you export an entity before it has been published, you will get an 'Invalid Argument' error when importing the entity if it has any relationships.

The reason for this is that the relationship attribute will have no display name, which results in the error. The simplest solution would be to go back to the original deployment, publish the entity, then export again. But, if that's not an option, you can fix the problem within the exported xml as follows:

The invalid relationship xml will look like the following:

<field name="pjv_targetid" requiredlevel="required" imemode="auto" lookupstyle="single" lookupbrowse="0">
<displaynames />
</field>

The problem is that the displaynames element is blank. This can be changed to something like the following

<displaynames>
<displayname description="Target" languagecode="1033" />
</displaynames>

The languagecode attribute will need to be set to the correct value for your deployment. Many other elements will have this value set in the customizations.xml file