How to resolve 2000 items resolution in BCS

If you are working with a larger external list (more than 2000 items) in dev environment it works perfectly, but when you work in production environment, it throws some of the following errors and added in SharePoint Logs.

·         Timeout expired. BDC Runtime Object Model has throttled the response. The response from the service took more than '180000' milliseconds. The limit can be changed via the "Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

·         WCF Service Connector has throttled the response. The response from the WCF service contains more than '3000000' bytes. The maximum amount of data that can be read through WCF Service Connector is '3000000' bytes. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

·         Web Service Connector has throttled the response. The response from the web service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through Web Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.

·         Opening of a new connection is throttled. Maximum enabled connections ‘200’ for proxy are already been opened.

·         Database response throttled. Maximum number of rows that can be read through the database system utility is 2000.

This type of error normally occurs when BCS has throttled an external system call for taking too long, or trying to transfer too much data. By default this feature is enabled to prevent Denial of Service attacks that could adversely affect SharePoint or external system health by performing huge transactions. If you have a business need, you may want to increase the limits or disable them entirely, but keep in mind that you increase your farm’s exposure to Denial of Service threats.

To change that limit to a higher value, you need to be Farm Administrator and only can do the required changes using public APIs or PowerShell (SharePoint Designer and Central Administration UI won’t do it).

Normally when you start creating external lists you will notice that out of the box SharePoint limits external lists to 2,000 items, these are generally the first 2,000 items from the external content source. This limit is placed on External Content Types created in SharePoint Designer. The Business Connectivity Service Application has two limits, one for External Content Types created using SharePoint Designer and one for External Content Types created using code (Visual Studio). These limits can be changed using PowerShell as seen in the example below

 $bcs = Get-SPServiceApplicationProxy | where{$_.GetType().FullName -eq (‘Microsoft.SharePoint.BusinessData.SharedService.’ + ‘BdcServiceApplicationProxy’)}

$BCSThrottle = Get-SPBusinessDataCatalogThrottleConfig -Scope database -ThrottleType items -ServiceApplicationProxy $bcs

 $BCSThrottle

 This will output the current limits. By default 2,000 with a maximum of 1,000,000 items if using Visual Studio. You can change the default values using the commands below. In this example we have increase the default limit to 20,000 items

 Set-SPBusinessDataCatalogThrottleConfig -Identity $BCSThrottle -Maximum 1000000 -Default 20000

 You now need to get the BCS Throttle Config data again

 $BCSThrottle = Get-SPBusinessDataCatalogThrottleConfig -Scope database -ThrottleType items -ServiceApplicationProxy $bcs

 Now run the command below to check your new limit.

$BCSThrottle

Scope        : Database

ThrottleType : Items

Enforced     : True

Default      : 20000

Max          : 1000000

This will increase the amount of items that can be retrieved in the browser, however there will still be a limit on the amount of items that can be taken offline using Outlook or SharePoint Workspace. This limit is still going to be 2,000 items. Unfortunately this has to be changed on each client by making a change to the registry. (thanks for Shane Young for blogging about this http://msmvps.com/blogs/shane/archive/2010/10/19/bcs-and-sharepoint-workspaces-limited-to-2000-items-from-external-list.aspx )

 Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\

Create a new “Key Business Data” with another sub-key “Synchronization”  (remove the quotes of course)
Now create a new Dword called “Query Instances Limit” set the decimal value to the number of items you want a user to be able to take offline. This is up to 30,000 with SharePoint Workspace.

Hope this was helpful! Stay tuned for more learning posts on BCS and External Lists.

How to Insert Item in Sharepoint List using WebService with Javascript

Without using Sharepoint API we can achieve by OOB. In following code I have inserting items in MyList and ID and Title.

You can call the from a button with the following code

<input type="button" value="Save List Item" onclick="javascript:SaveListItem();">

function SaveListItem()
{
var soapRequest = '<?xml version="1.0" encoding="utf-8"?>' +
'<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">' +
' <soap12:Body>'+
' <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">'+
' <listName>TestList</listName>'+
' <updates>'+
'<Batch OnError="Continue">'+
' <Method ID="1" Cmd="New">'+
' <Field Name="ID">New</Field>'+
' <Field Name="Title">TestData</Field>'+
' </Method>'+
'</Batch>'+
' </updates>'+
' </UpdateListItems>'+
' </soap12:Body>'+
'</soap12:Envelope>';
xmlHttp=new XMLHttpRequest();
xmlHttp.open('post', 'http://ServerName/SiteCollection/_vti_bin/Lists.asmx', true);
xmlHttp.setRequestHeader('Content-Type','application/soap+xml; charset=utf-8');
xmlHttp.send(soapRequest);
}

How to Delete attachment from an Item in Sharepoint List

public static void DeleteAttachements(int id)

{
using (SPSite site = new SPSite("http://portal"))
{
using (SPWeb web = site.OpenWeb())
{
SPListItem listItem = web.Lists["mylist"].GetItemById(id);
List<string> fileNames = new List<string>();
foreach (string fileName in listItem.Attachments)
{
fileNames.Add(fileName);
}
foreach(string fileName in fileNames)
{
listItem.Attachments.Delete(fileName);
}
listItem.update();
}
}
}

How to Update User Profile using C#

using (SPSite site = new SPSite(“http://portal”))

{
Microsoft.Office.Server.ServerContext Context = ServerContext.GetContext(site);
Microsoft.Office.Server.UserProfiles.UserProfileManager Mngr = new UserProfileManager(Context);
Microsoft.Office.Server.UserProfiles.UserProfile Profile = Mngr.GetUserProfile(“litwareinc\ashishk”);
System.Console.Write(Profile[“Title”].Value.ToString());
Profile[“Title”].Value = “New Title”;
Profile.Commit();
}


Bingo, here ashishk Title is updated with "New Title"

MOSS Expiration Issue after installing SP2 for MOSS 2007

Many people are facing problem MOSS expiration After installing SP2 for SharePoint Server 2007, because it incorrectly activates the product expiration date. This means after 180 days the SharePoint Server will become inaccessible to all end users.

As per Microsoft KB Article, follow the steps as mentioned below

• Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.  
• Click the Operations tab. 
• Click Convert License Type.  
• In the Enter the Product Key text box, type your product identification number, and then click OK.  
• The License Synchronizer Job will run on all computers in the farm after a few moments. After all the computers have updated their license from the timer job, the Convert License Type page will reflect the correct license.

For more informatin, review following article
http://support.microsoft.com/kb/971620

http://blogs.msdn.com/sharepoint/archive/2009/05/21/attention-important-information-on-service-pack-2.aspx

Download Document from SharePoint Library using WebService

In  following code, I am reading the contents from the documents and creating and storing documents in local system

//Copy WebService Settings
string webUrl = "http://portal/";
WSCopy.Copy copyService = new WSCopy.Copy();

copyService.Url = webUrl+"/_vti_bin/copy.asmx";

copyService.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Source and Destination Document URLs
string sourceUrl = "http://localhost:1000/Shared%20Documents/Sample.doc";
string destinationUrl = "C:\\Documents\Sample.doc";


//Variables for Reading metadata’s of a document
WSCopy.FieldInformation fieldInfo = new WSCopy.FieldInformation();
WSCopy.FieldInformation[] fieldInfoArray = { fieldInfo };
WSCopy.CopyResult cResult1 = new WSCopy.CopyResult();
WSCopy.CopyResult cResult2 = new WSCopy.CopyResult();
WSCopy.CopyResult[] cResultArray = { cResult1, cResult2 };


//Receive a Document Contents into Byte array (filecontents)
byte[] fileContents = new Byte[4096];
copyService.GetItem(sourceUrl, out fieldInfoArray, out fileContents);


//Create a new file and write contents to that document
FileStream fStream = new FileStream(destinationUrl, FileMode.Create, FileAccess.ReadWrite);
fStream.Write(fileContents, 0, fileContents.Length);
fStream.Close();

How to integrate Google Map

To integrate Google Map with MOSS is very easy, you only need to add to content editor web part with some piece of HTML. For more information see following url

http://vspug.com/bobbyhabib/2007/12/05/create-a-google-map-web-part-for-free/

How to modify the values of "Created By", "Modified By" columns in SharePoint lists

SPSite oSite = new SPSite("http://portal");
SPWeb oWeb = oSite.OpenWeb();
SPList oList = oWeb.Lists["mylist"];
SPListItemCollection oListCollection = oList.Items;
foreach (SPListItem oListItem in oListCollection)
{
SPFieldUserValue oUser = new SPFieldUserValue(oWeb, oWeb.CurrentUser.ID, oWeb.CurrentUser.LoginName);

// or you can hard code the value like this,

SPFieldUserValue oUser = new SPFieldUserValue(oWeb, 14, "Ashish Kanoongo");
oListItem["Author"] = oUser;//created by - "14;#Ashish Kanoongo"
oListItem["Editor"] = oUser;//modified by - "14;#Ashish Kanoongo"
oListItem.Update();
}
oWeb.Update();

How Can I Show All Items with/without Folders in SharePoint?

To implement this we have 3 options

Option 1
Using SPQuery object and setting ViewAttributes property in it; You can retrieve only the folders from the SharePoint List, with a single condition.

SPSite site = SPContext.Current.Site;
SPWeb web = SPContext.Current.Web;
SPList list = web.Lists["Shared Documents"];
SPQuery query = new SPQuery();
//Condition to check the item type is folder or not
query.Query = "<Where><Eq><FieldRef Name=’FSObjType’/><Value Type=’Lookup’>1</Value></Eq></Where>";

//Get all the items including subfolders from the list
query.ViewAttributes = “Scope=’RecursiveAll’";
//Retrieve the items based on Query SPListItemCollection items = list.GetItems(query);


string folderDetails="";
//Get the name and Url for the folder
foreach (SPListItem item in items)
{
folderDetails += "Folder Name:" + item.Name + "<br/>Folder URL:" + web.Url + "/" + item.Url + "<br/>";
}

In Query property of SPQuery object, You can set 0 for folder (if item type  = folder) or 1 for List Item/Documents(if ItemType=List Item).

We have set Scope=’RecursiveAll’, which allows to retrieve all the items and folders from the list or library.

Option 2
1. Open Data Source Details pane in Menu > Task Panes > Data Source Details.
2. Click the data source listed in Current Data Source.
3. In the opening Data Source Properties pane, change the item and folder scope from Files Only to Recursive
4. Save and exit.

Option 3
Other than dataview webpart you can also set options directly in views also. To implement this we can take example of "All Documents", follow the steps1. Select "All Documents" view
2. Select "Modify This View"
3. Scroll down whole page
4. Expand Folders
5. Then select "Show all items without folders"
6. Click OK button 

Microsoft Office SharePoint Server 2007 and Related Technologies pricing

http://office.microsoft.com/en-us/sharepointserver/FX102176831033.aspx

Sharepoint Price Calculator

http://community.bamboosolutions.com/blogs/sharepoint-price-calculator/default.aspx

Create SharePoint Alerts Programmatically

Using following code you can create alerts

SPUser user = mySite.Users["domain\user"];
SPAlert newAlert = user.Alerts.Add();
newAlert.Title = list.Title;
newAlert.AlertType = SPAlertType.List;
newAlert.List = list;
newAlert.Properties["eventtypeindex"] = "1";
newAlert.AlertFrequency = SPAlertFrequency.Immediate;
newAlert.Update(false);
 
If you want to set an alert for a list, you have to set the AlertType property to SPAlertType.List and set the List property to an instance of the SPList class.
 
If you want to set an alert for a list item, you have to set the AlertType property to SPAlertType.Item and set the Item property to an instance of the SPListItem class, in that your code should be
 
newAlert.AlertType = SPAlertType.Item;
newAlert.Item = item;
 
You can change following codes depending on what action you want to set alert using "eventtypeindex"
 
all = 0
added = 1
modify = 2
deleted = 3
web discussions = 4

If you want to send Alert daily you can change the AlertFrequency as mentioned below

newAlert.AlertFrequency = SPAlertFrequency.Daily;
newAlert.AlertTime = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, 8, 0, 0);

If you are subscribing alerts for some other user, you should be the administrator or should have permisssions to manage alerts thne add your code with following code

SPSecurity.RunWithElevatedPrevilages(delegate()
{

web.AllowUnSafeUpdates=true;
  // Add your code here
});

How to Shrink SharePoint content database size?

Run following SQL command to free up space of sharepoint content database

dbcc shrinkdatabase(WSSContentDatabaseName)

How to Hide Fields in SharePoint List Form Pages using jQuery

Yesterday, one of friend ask me can we show/hide fields in Newform.aspx and Editform.aspx by clicking on checkbox. It can be done thru sharepoint designer with some rules. But I used jQuery with few lines of code in Task List. I added 1 more field i.e. ShowFields as choice field.

Then I added a content editor webpart on newform.aspx. MOSS wont allow to edit newform.aspx and editform.aspx. To add webpart on newforn.aspx / editform.aspx, add in &ToolPaneView=2 on the end of the url. So your url for newform.aspx will be http://portal/Lists/Tasks/NewForm.aspx?RootFolder=%2FLists%2FTasks&Source=http%3A%2F%2Fportal%2FLists%2FTasks%2FAllItems%2Easpx&ToolPaneView=2

In source editor of content editor web part, I have added following script

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script>

<script>
$(document).ready(function()
{
$("input[title$='ShowFields']").click(function()
{
$("input[title$='Title']").parent('span').parent('td').parent('tr').toggle();
$("nobr:contains('Start Date')").parent('h3').parent('td').parent('tr').toggle();
$("nobr:contains('Due Date')").parent('h3').parent('td').parent('tr').toggle();
});
});
</script>

and close the webpart page. Then open newform.aspx, now whenever I click on Showfields Checkbox then Title/Start Date/Due Date fields are hidden, once I uncheck this it shows the fields again.

How to set permission and restrict view to particular user

Using following tool you can set permissions to views and restrict views to particular users. This feature is not available in MOSS 2007.

http://www.codeplex.com/SPViewPermission

How to set field permission in Sharepoint List

Folllowint tool providing advanced settings to customize list form rendering in new, display and edit mode.

http://splistdisplaysetting.codeplex.com/

Disable Right Click on Sharepoint Portal

Add a content editor web part and add following code in the source editor

 

Microsoft Best Practices Analyzer for 2007 Microsoft Office System

The Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System Best Practices Analyzer programmatically collects settings and values from data repositories such as MS SQL, registry, metabase and performance monitor. Once collected, a set of comprehensive ‘best practice’ rules are applied to the topology.

Administrators running this tool will get a detailed report listing the recommendations that can be made to the environment to achieve greater performance, scalability and uptime.

http://www.microsoft.com/downloads/details.aspx?familyid=cb944b27-9d6b-4a1f-b3e1-778efda07df8&displaylang=en

How to turn off custom error message

Take a backup of web config now follow these steps to do that:


Open the web.config
Find "customErrors" and turn those "off".
Next do a search for "callStack" and set the value to "true"

Save the web.config and you are ready to go.