SharePoint and Access

This post will concern SharePoint for once in a while!

As most of you know, it is possible to link SharePoint lists to excel, however, it is also possible to link SharePoint lists as tables in Access. This is very usefull since you can then create more advanced views using complex SQL. Data can also be linked with data from other sources, if needed. There should also be some ODBC driver that could be used to fetch data from a SharePoint list, however, I havn't seen one. If I do, I will let you know.

How to link a SharePoint list into Access as a table:
1. Make a new view in SharePoint with all columns and all rows. Call it something nice like "everything".
2. In access, choose the file menu and then choose "Get external data" (I am using the swedish version of access, so excuse my translation) and then choose "Link tables". In the dialog that opens, you can choose SharePoint list from the filetype dropdown. Then enter the url for the site, not the list. You will then be prompted for which list you want to use and so on and so forth. When done, you will have one maybe more linked tables in Access which will work just as any other tables in access.

Happy SQL:ing!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Developing with VS 2005

Extending MS CRM 3.0 is normally done using Visual Studio 2003 and this is also the only supported development environment. However, it is possible to develop using VS 2005 as well. Some things have to be considered.

.NET 2.0 will work fine for external web applications using the CRM Webservice (not very strange since the webservice is generic)

.NET 2.0 will probably also work fine when developing workflow dll:s, but it is not supported. At least it worked when I tested Hello World! :)

.NET 2.0 will not work when developing callouts. However, there is a great description of how to develop these in VS 2005 anyway but for .NET 1.1. Beware that you cannot use specific .NET 2.0 functionality.

Here is the site: http://blogs.msdn.com/arash/archive/2006/08/25/719626.aspx

A good thing with this is that the standard TDT can be used when developing customizations. I am no licensing expert, so make sure it's within your licensing agreement.

Gustaf Westerlund CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Odd SQL Reporting Services Error...

I was working with a custom report today and I had a very strange error which I have noticed that several others also seem to have, both with SQL Reporting Services and other .NET applications. The report I was making worked fine, but when I tried to deploy it to the site, I got the following error:

"Length cannot be less than zero."

When I dubbel-clicked it in VS2003 (yes, I am still using it), the sourcefile of my DataSource was shown, and there was nothing wrong there...

Well, I tried lots of different things, restarting, trying to deploy to another server but the error was still the same and I could see the report was working fine i Preview mode.

Well, after a while I got fed up with it, and just tried to install the rdl manually on the reportserver (using the update function, in the properties tab of the report). It worked!

Hence, there seems to be some error in the deployment of reports in SQL Reporting Services. If you get this error, try to bypass it, your report might not be the problem after all.

Anyone reading this that knows why the deployment didn't work? I'd like to know!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Web and Outlook mixup

As some of you might have noticed, if you have the Outlook desktop client installed and Outlook open, and then open CRM using IE, you will loose some web-specifics like the "Settings" menu and other addons specified in the sitemap or isv.config.xml as "web".

This is due to the fact that these files are downloaded to the client by IE when Outlook is started and these are then cached by IE. When the same URL is opened, IE recognizes these files, and does not download any new files, hence you get the Outlook-client specific.´

How to solve this? IE bases its caching on the url so you just have to install the crm-outlook-client to one url and use the webclient for the other. Note that you can even use the IP adress as one of the url:s, since IE doesn't resolve the name when checking for cache-hits.


Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Downloading the IE fix

I read the CRM Team Blog and they have a link to the IE-fix concerning the bug discussed bellow.

http://www.microsoft.com/downloads/details.aspx?familyid=ff9bc431-01f3-48e8-9a58-d701d2e60c1d&displaylang=en

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Use AllColumns

When retrieving data from the CRM webservice, part of the
RetrieveRequest-object needs to have the required columns specified. In
many of the examples this field is specifically set to the columns
needed, which is the most "correct" way of retrieving the data. However,
if you aren't experiencing performance problems the easiest way is to
use the AllColumns-class.

In short:

RetrieveRequest retrieve = new RetrieveRequest();
retrieve.ColumnSet = new AllColumns();

This is probably old news to all of you who have coded a bit. But I
thought I might write a bit about it anyway, since not all people are
experts at birth.

Gustaf

more on SubscriptionSyncInfo

Well, sometimes it's better to look first and blog second,
I looked around a bit and found that the CRM Team Blog actually have an entry on how this works. Have a look here: https://blogs.msdn.com/crm/archive/2006/08/15/701230.aspx

The SubscriptionSyncInfo table seems to be the place where all syncs are logged to the database. Very usefull for checking if a users sync has been successfull and how often it has been done.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Subscription Database tables

There are a couple of tables in the database that handle the Outlook-CRM Syncronization and the offline sync. I havn't been able to find any further documentation on exactly how it works, but will publish any more info I find on the subject on this blog. There are mainly three normal tables concerned, namely:

Subscription
SubscriptionClients
SubscriptionSyncInfo

There are also several tables created for each user that contains their specific sync data. These are named:
SyncEntry_987F788E642CDB1184900007E94DD8DB

I would like to point out that these tables both contain info on Outlook-CRM sync and CRM Offline sync (ie Main SQL - MSDE sync).

If anyone has any more info on the subject, please contact me, I would like to discuss the matter.

If I find the time, I would like to research this a bit more. If I do, I'll let you know...

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

JavaScripting and quick create forms
For you who haven't already noticed, if you create an OnLoad JavaScript for Account, Contact or any other entity and it includes some sort of defaulting of field values, if this field is not compulsory, it is not included in the quick create form, hence the onLoad event will send an error when the form is loaded. So, just check that it is there first :)

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se