CRM Error: Generic SQL Error Code: 0x80044150

So, I have seen and checked very proposed solution to this error on the web.

The only problem associated with this issue I did not see any record of is of course the error I had with a customer recently...

The issue is complicated-simple issue...

Simple explanation would be:
Once you export and entity, add a new attribute with the same name as an existing one (not case sensitive - meaning 1= new_myattribute 2=New_myattribute) you will get this error.

Simple, Right?

Now, for the complicated way of getting this error...

Normally, the CRM will lower-case all letters in the attribute and add the "new_" prefix for you.

So - when looking for a property to check if it exists I used to have a lower-case case sensitive XML XPath search for it, and if I did not find it - I knew it was safe to add it...

Only that apparently the CRM 3.0 and CRM 4.0 has some inconsistency with the prefix of custom attributes...

One adds new_ while the other adds New_, this means that one of our customers who used the CRM SharePoint connector in CRM 3.0, upgraded the solution to CRM 4.0, and run our solution again - ended up with this error message since he had the old field from the upgrade and we were trying to add it as duplicated in the new version since we could not find it...

Well - complicated enough?

I will keep the conclusion short -
1: Be sure not to add duplicate attributes, or import will fail.
2: When looking for attributes - don't use case-sensitive comparison (although tempting in XML)!
3: Email the CRM developer guys and request nicely for better error messages ( don't tell them I sent you :) )

Well, Please feel free to post here any other problems / solutions related to this very generic error code.

Just for the record - the error code mostly refers to trying to assign data into a field that is too long... so if you didn't check that first - this would be a good time to do so... :)

Error using web part connection in list item form

Ever go this error when using a connection?
"Web Part Error: This page has exceeded its data fetch limit for connected Web Parts. Try disconnecting one or more Web Parts to correct the problem."

Well, it is known that the filter connections sometimes invoke this, and there are ways to fix it... but - I just found a "fluke" in the SharePoint connections infrastructure that is not caused by bad-coding at all.

As a matter of fact - I can reproduce it using only out of the box web parts and no custom code at all!

We wanted to have in our customers list, when a user go in to the display form of a customer, to get a list of contacts associated with this customer from the contact list.

So - we added the List View web part of Contacts list into the DispForm.aspx of the Customer list.

Than we used Custom Item Property (more information on www.kwizcom.com) that gets the current customer's title and sends it as a filter to the List View web part...

Sounds simple, only to find out that using List View web part on a display form, with a filter connection - is apparently (and unspoken) not supported!!!

It will always give an error "Web Part Error: This page has exceeded its data fetch limit for connected Web Parts. Try disconnecting one or more Web Parts to correct the problem.".

This has nothing to do with my code ( I found out couple of hours later ) since if I try it using the Query string filter web part - I get the same result every time.

Ok, after a while, I started looking for other creative solutions and found a work-around for this issue.

Converting the List View web part to a Data View web part will solve this issue and will allow you to add a filtered view of related information!!!

(Sorry for the !!!, just excited that it is not my code that caused the issue :) )

(* hint: to convert list view into data view - open the page in SharePoint Designer, right click on the list view and select the "convert to data view" option... )

Well, hope this helps save some lives, keep me posted if you have additions / questions,

Shai Petel,
VP Professional Services
KWizCom

Export SharePoint list to excel using DataGrid

From time to time I learn that the OOTB export to excel capabilities of SharePoint are missing some edge.

For instance - the latest thing my colleague found is that if you do not have excel 2007 but earlier versions, it does not export some column types correctly, or are completely missing.

So, I have created a simple ASP.NET web part that will allow the user to export the list items / list view into excel, that does not require a specific version of excel to be installed on the client.

The concept is simple:
Connect to a list / view,
Get items as DataTable
Connect DataTable to a DataGrid
Export DataGrid to excel.

So, to get the list items / view items into a Data Table, I used one of these code samples:

For list URL + view ID:

using (SPSite site = new SPSite(txtListUrl.Text))
{
  using (SPWeb web = site.OpenWeb())
  {
    SPList list = web.GetListFromUrl(txtListUrl.Text);
    DataTable items = list.GetItems(list.Views[new Guid(cmbListView.SelectedValue)]).GetDataTable();

    ExportToExcel(items);
  }
}

Or, if you plan to place the WP in the list view page - here it is working with the SPContext.Current.List:

SPList list = SPContext.Current.List;
DataTable items = list.Items.GetDataTable();

ExportToExcel(items);

Ok, so now we have the DataTable, all we have to do is implement the code that will export the DataTable to excel using Data Grid:

private void ExportToExcel(DataTable items)
{
  theGrid = new DataGrid();
  theGrid.DataSource = items;
  theGrid.DataBind();

  Page.Response.Clear();
  Page.Response.ContentType = "application/vnd.ms-excel";
  Page.Response.Charset = "utf-8";
  Page.Response.AddHeader("Content-Disposition", "attachment;filename="+DateTime.Now.ToString("yyyyMMdd_hhmmss")+".xls");

  System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter oHtmlTextWriter = new   System.Web.UI.HtmlTextWriter(oStringWriter);
  theGrid.RenderControl(oHtmlTextWriter);

  Page.Response.Write(oStringWriter.ToString());
  Page.Response.End();
}

And guess what? It is that simple!

Enjoy,

Shai Petel
KWizCom VP Professional Services