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

More CRM MVPs

This a belated welcome to all recently awarded CRM MVPs. At the time of writing there are now 29 of us worldwide. I've updated the blog list to reflect the new MVPs - as far as I can tell these are up-to-date, but let me know if not

Using the Reporting Services Execution Log with CRM 4.0

I've contributed another article to the Microsoft CRM Team Blog. This is about making good use of the Reporting Services Execution Log with CRM 4.0 reports.

In the past I've found the execution log to be very useful for getting statistics on when reports were run, and the overall performance. However, CRM 4.0 introduced some complexities around using the log effectively. One issue is that the report name is not stored in Reporting Services, and another issue is that, if you use the Reporting Services connector, then the user name is not recorded in the execution log. The article describes how to resolve these issues.

I've added some support files for this to the MSDN Code gallery

Report Builder 2.0 for SQL 2008 - No need for a report model

I've always considered the Report Builder tool that ships with SQL Reporting Services to be a good tool for generating reports without SQL knowledge, and in the context of CRM it's much more powerful than the CRM 4.0 Report Wizard.

One drawback of the Report Builder for SQL 2005 was that it needed a Report Model to abstract the underlying data, and Report Models are not dynamic (see below for why this was a pain). One major advantage of SQL 2008 is that it ships with a new version of Report Builder, Report Builder 2.0, that allows direct connections to the SQL database. This will make it a lot more useful in CRM deployments, especially when your schema changes. A good overview of the use of Report Builder 2.0 can be found here.

Why Report Models are a pain with CRM
Earlier, I said 'Report Models are not dynamic' Put another way, if you built your Report Model on your CRM database, then added a new attribute to an entity, you would have to regenerate your Report Model. In itself this wouldn't be too bad, but when you generate a Report Model from a filtered view you end up with a lot of unnecessary attributes, and a fair bit of work to do to provide a sensible set of attributes in the Report Model. To get round this we wrote a set of tools to manipulate the Report Model files directly and automate a lot of the rebuilding. This saved a lot of time, but still required a deployment process to allow reports against new attributes or entities.

Deploying Custom Workflow Activities - Add authorizedType

This is the first of an intended two posts about registering custom workflow activities, and regards the initial deployment; a subsequent post will address updates and versioning issues.

The CRM 4.0 SDK gives a reasonable overview of registering a custom workflow activity, covering the following steps:

1. Building / Deploying a plugin registration tool
2. Registering the assembly
3. Adding referenced assemblies to the GAC
4. Stopping and restarting the CRM Async Service

However, one important point is omitted - adding your activity classes as an authorizedType within the web.config file. Workflow in CRM 4.0 uses the Windows Workflow Foundation (WWF) which, as a relatively new .Net technology, includes a reasonable security model to reduce the risk of malicious code being deployed within it.

The security model in WWF will only allow a permitted list of classes to be called as custom activities. In CRM 4.0 this list is stored in the web.config file in the root of the CRM website, and looks like this:

<System.Workflow.ComponentModel.WorkflowCompiler>
<authorizedTypes>
<authorizedType Assembly="System.Workflow.Activities, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Workflow.Activities" TypeName="IfElseActivity" Authorized="True"/>
<authorizedType Assembly="System.Workflow.Activities, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Workflow.Activities" TypeName="IfElseBranchActivity" Authorized="True"/>
</authorizedTypes>
</System.Workflow.ComponentModel.WorkflowCompiler>

For your custom activity to be permitted, you have to add an entry as an element; for example:

<authorizedType Assembly="MyAssembly, Version=4.0.0.0, Culture=neutral, PublicKeyToken="7766554433221100" Namespace="MyActivities" TypeName="*" Authorized="True"/>

Note that I've used an asterisk (*) to indicate all classes in the given namespace and assembly are permitted; you could reference an individual class if you prefer. Also note that the assembly is referenced by the strong name, as the assembly has been digitally signed when it was compiled.

This raises the question 'what happens if you don't do this step?' If you don't, you can deploy the workflow plugin, and use it in a workflow rule, but you get an error when you try to publish the workflow rule. Unfortunately the message you get is unhelpful; it's a variation of 'An unexpected error has occurred'. If you dig deeper, and enable tracing on the server, you do get a useful message in the w3wp log, such as the following:

Workflow compilation failed:WF363: Type MyActivities.Demo, MyAssembly, Version=4.0.0.0, Culture=neutral, PublicKeyToken="7766554433221100 is not marked as authorized in the application configuration file.

Once you find it, this message is pretty useful, as it tells you most of the problem, and also references the assembly in exactly the same way as you need to in web.config