Friday, 18 December 2009
I've not had a chance to test it yet. One welcome inclusion is an optimisation to the Import Organisation process (http://support.microsoft.com/kb/977867/ ), though interestingly you have to add a registry value to enable the optimisation.
There's also appraently a fix to the 'dsMain' error with Report Wizard, as described here
Thursday, 3 December 2009
Wednesday, 2 December 2009
Soon, I'll post a more detailed description of SQL performance analysis for CRM implementations, which will reference this tool.
Friday, 13 November 2009
Despite my major reservations with how the problem arose, credit is due to Microsoft for responding as quickly as I think they possibly could (even if it doesn't always seem that way).
My reservations relate to disagreements on the need for the change made in UR7 (I don't think it increases security at all), and also to how MS allowed a change that broke supported extensions. It'll be interesting to see what happens with UR 8...
There are 2 solutions:
- As suggested in the link above, add registry values to give a much wider range of socket addresses. Note that you need to restart the server for these registry changes to take effect
- Set the UnsafeAuthenticatedConnectionSharing and PreAuthenticate properties of your CrmService (or MetadateService) proxy to true. This will allow your web request to reuse the same socket
Thursday, 12 November 2009
As I was one of the document's technical reviewers, I may be a bit biased in my praise of the document, however one option is missing from it. As a Microsoft document, it concentrates on the supported approaches, but there are also unsupported approaches, such as that taken by c360, which uses an HttpModule to modify the html sent to the client. Personally I dislike this approach, as there is a potentially heavy performance overhead, and it is fragile with respect to future changes in hotfix rollup. However, it is another option that could be considered, and is unfortunately not mentioned in the white paper.
Friday, 6 November 2009
It was going to happen at some point; a CRM hotfix rollup that broke existing supported customisation code. Update Rollup 7 adds some security changes that apply to the Attachment/download.aspx page. As a result the example code documented here in the SDK no longer works.
If you try the code, the user gets the error message "Access Denied, Invalid Operation". If you enable tracing, you see a message like the following:
"Message: INVALID_WRPC_TOKEN: Validate WRPC Token: WRPCTokenState=Invalid, TOKEN_EXPIRY=4320, IGNORE_TOKEN=False, TOKEN_KEY=b1Nd0byfEd6+gwAZuez7cauYiRnfHuMLAquFY1Ks22dHgxZiW5IrxSobkv9aVfbC, ErrorCode: -2140991221"
The fundamental problem is that CRM now expects additional parameters on the query string that contain a Token, and there is no way for us to generate that token. There's a significant question about why this change was made, but I'll leave that for another time.
The recommended workaround is the use the web services directly as described at http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/2f46a3c4-1123-49dd-804e-2787ef367986, which I expect to make it into the SDK soon. This is close to a like-for-like replacement for the original SDK code in that it is the type of code you might write for a console application.
However, a popular use of download.aspx was to provide any easy client-side link to download an attachment, rather than doing it programmatically with .Net code. To get equivalent behaviour you'll need a custom .aspx page that gets the uses similar code to that above, but writes it the response stream using Response.BinaryWrite. I'm going to have to write this code soon, and when I do I'll post it on the MSDN Code Gallery, and link to it here; in the meantime if anyone has to write it themselves the other thing you should do is pass information on the Response-Header to specify the filename. From memory this involves something like:
Response.AddHeader("content-disposition","attachment; filename=" + outputFile);
Thursday, 5 November 2009
There is no 64 bit OLEDB provider for Excel
So the packages cannot be executed by 64 bit code. This meant that the programmatic route for running packages with the classes in the Microsoft.SqlServer.Dts.Runtime namespace no longer works. The error you get is typically unhelpful - 'AcquireConnection method call to the connection manager "EXCEL: Source" failed with error code 0xC00F9304'. You can also get error code 0xC020801C.
The workaround is to run a package using the 32 bit version of dtexec.exe, which should be installed in Program Files (x86)\Microsoft SQL Server\100\DTS\Binn. This is also the fix if scheduling packages with the SQL Agent; your job steps need to be of type CmdExec instead of SSIS Package, and you have to learn the command line syntax of dtexec.
Overall, something of a pain, but I've not yet found anything I can't do with dtexec.exe and its command line parameters.
The Registry settings for the 32 bit OLEDB provider for Excel have moved
There are a several registry settings that you may need to make the OLEDB provider for Excel work, as described here and here. When running a package in 32 bit mode on a 64 bit server, you find that it uses registry keys in a different location.
Values that were in HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel are now in HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.
Note also that the paths to the providers have changed, so for example "Win32"="C:\Windows\system32\msexcl40.dll" becomes "Win32"="C:\Windows\syswow64\msexcl40.dll"
Although my emphasis here has been on Excel providers, and SSIS, the issues and resolutions can apply to other OLEDB providers and environments, as there are several OLEDB providers that are not expected to have 64 bit releases (the Exchange OLEDB provider, and the FoxPro one are 2 that I'm aware of)
Tuesday, 22 September 2009
This is another post on design patterns (like this one), looking initially at potential data integration approaches and key issues, then covering some specifics on the reporting approach.
Thursday, 3 September 2009
I was interviewed for the Dynamics World web site recently, so I figured I should add a link to the interview here
Tuesday, 18 August 2009
The main point is that the NTAuthenticationProvider attribute can be viewed or set at several levels via the adsutil.vbs script. This is reasonably well documented in the Technet article referenced from the EDW help file. However, what is not made clear is that the attribute can be set at one of 3 levels. Each of the following commands does something slightly different:
cscript adsutil.vbs set w3svc/WebSite/root/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/WebSite/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/NTAuthenticationProviders "Negotiate,NTLM"
The third command is noticeably different, in that it sets the attribute at the server level, rather than the web site level. I can't see any particular reason for different behaviour between the first 2 commands, but it does affect the CRM EDW. As far as I can tell, you need to use the syntax in the second command to satisfy the EDW, whereas unfortunately the Technet article uses the syntax in the first command.
* Re what Authentication Provider to use. I've happily run a single server implementation of CRM using NTLM authentication, but I expect Kerberos may be necessary in a multi-server implementation
Friday, 7 August 2009
Tuesday, 4 August 2009
The results of these investigations have been posted on the Microsoft Dynamics CRM Team Blog. I also created a couple of reporting services reports to display the privilege data by role. These are available on the MSDN Code Gallery
Thursday, 30 July 2009
The SDK documentation states this attribute is of type CrmNumber, so when writing a QueryExpression that selects only records with a CustomizationLevel = 1, it would seem reasonable to use something like:
qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, 1));
However, this gives the error “Condition for attribute 'customizationlevel': expected argument(s) of type 'System.Byte' but received 'System.Int32' " (code 0x80040203 - Invalid Argument). Digging deeper I found that the field for the CustomizationLevel is stored in SQL as a tinyint (i.e. a single-byte integer), and that the AttributeTypes table in CRM has a corresponding AttributeType of tinyint.
So, despite the attribute being identified as a CrmNumber, any condition expressions need to pass values as a single-byte integer, not the documented four-byte integer. This is easily done by using the following:
qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, (Byte) 1));
Being somewhat nosey, I thought to see what other attribute types there were. These can be easily found with the following SQL query:
Select * from AttributeTypes
In addition to tinyint, 2 similar types caught my attention – smallint and bigint (SQL Server data type names are not that imaginative). Following on from this, the following SQL query lists attributes of these types, which may cause similar problems to those above:
select e.name as Entity, a.name as Attribute, at.description as [Type]
from attribute a join entity e on a.entityid = e.entityid
join attributetypes at on a.attributetypeid = at.attributetypeid
where at.description in ('tinyint', 'smallint', 'bigint')
order by at.description, e.name, a.name
This yields the following results. The bigint attributes aren’t a concern, as these 2 attributes aren’t available via the CRM platform, but you could encounter some of the smallint types on UserSettings, in which case I expect you’d have to cast values to Int16.
Thursday, 23 July 2009
Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:
select convert(varchar(max), convert(varbinary(max), content))
where content is not null
Sunday, 5 July 2009
It's good to see several other CRM MVPs ahead of me on the list - you can find out who they are here
Friday, 3 July 2009
As several others have announced, CRM 4 Update Rollup 5 has been released. There are a couple of notable points to make:
- As with update rollups 3 and 4, the majority of the fixes have not been previously released as an individual hotfix. This is different from rollups 1 and 2, which were mostly a collection of previously released fixes. In general, I think that having fewer individually released fixes is a positive thing, but that has to be traded off against the frequency of rollups (currently about every 6 to 8 weeks), which is difficult to keep up with. Across our customers, we have deployments covering 5 different rollup levels, plus some customers on CRM 3, which needs a fair bit of management, especially with regard to schema compatibility
- So, schema compatibility. Apparently, UR5 schemas cannot be imported into versions prior to UR2, but also, pre-UR2 schemas cannot be imported into UR5. I've got some investigation to do on this, but the most important factor for me is being able to use one development environment that can be used to export (and import if necessary) schemas to all rollup versions of CRM 4. Up to now I've rarely applied rollups to development environments, but it looks like from now on, UR2 will be a minimum requirement for a development environment. Either way, it makes life harder than it should be.
Tuesday, 16 June 2009
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:
- 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)
- 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)
- 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
- 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
Friday, 22 May 2009
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:
- 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
- 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
Monday, 20 April 2009
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
Monday, 2 February 2009
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).
Monday, 26 January 2009
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.
Friday, 23 January 2009
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)
fetch next from cur into @obj
while @@fetch_status = 0
set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'
-- grant select permission to public
fetch next from cur into @obj
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
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
,[Target] decimal(10,2) )
declare cur cursor fast_forward
for select TABLE_NAME from #excelsheets
declare @tbl sysname, @sql nvarchar(4000)
fetch next from cur into @tbl
while @@fetch_status = 0
-- 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
-- Cleanup and output results
select * from #tmp
drop table #tmp
drop table #excelsheets
Thursday, 22 January 2009
- 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
- The CRM 4.0 reports are stored in a sub-folder called 4.0. Click on this folder to see the reports
- CRM 4.0 specifies the ReportID Guid as the report name, and the friendly name is now in the Description fields
Thursday, 15 January 2009
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
Tuesday, 6 January 2009
- Decimal, e.g. -2147206371
- Hex with the 0x prefix - e.g. 0x80043B1D
- 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:
- Open the calculator in scientific mode
- Check the Decimal (Dec) option is set
- Paste in the error code - e.g. -2147206371
- Select 'Hex' to convert it to Hex, this will then look like: FFFFFFFF80043B1D
- Remove the leading FFFFFFFF. You can then find the resulting code, e.g. 80043B1D, in the error codes