Avoid computed fields in your index

I have a stored procedure that deletes records from different table. It perfectly working fine until I added an index to the table. What makes this issue confusing is I'm not able to replicate it in a different environment. So, let's start with the error that I got before I get side-track with my frustrations :)

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

I found this MSDN article (http://msdn.microsoft.com/en-us/library/ms190356.aspx) and the last bullet is leading me into something:

• When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.


It seems like the bullet above translates to this set of commands to address the issue:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF


The script above might work, but I’m not so sure if that is really what I wanted to do.... This approach will surely require some code modification and that would be a maintenance nightmare long-term.

So, I looked at the other angle of the problem. I checked if part of my index had included a computed column. VIOLA! I found one!

Removing those stink'n fields from my indexes resolved the issue.

My SharePoint Development Environment

I have researched on what would be the best way for me to setup a development environment for SharePoint - instead I ended up asking myself a question. Well, I guess the question is how much development I will be doing in SharePoint. Ohhhhhh Kkkkkk.... Hmmmm.

Well, let's just assume that I am a SharePoint developer. I do .NET programming for a living and I code a lot. But, SharePoint development isn't no longer just programming. Nowadays, a couple of clicks in SharePoint 2010 can deliver a web site that is dynamic enough that users can interact with each other using forms, discussion boards, etc.

I think there is a balance between a developer and admin work when working with SharePoint development. It's no longer just developer, developer, developer (sounds familiar?). SharePoint development will usually require a lot of configuration. Configurations are usually hard to backup.

So, here's my thought on SharePoint Development environment. I think virtualizing my SharePoint Development environment is a way to go.

From what I heard and read, virtualization has a lot of advantages however it takes resources such disk space, processor power and memory from my machine. Virtualization is also expensive for this kind of purpose - some may say it's overkill for a developer workstation. Nevertheless, here are the advantages of having a virtual SharePoint Dev Environment:

1. Easy to backup SharePoint server's configuration.
2. Can work multiple instances of SharePoint projects that has different stages of feature development
3. Quick and easy way to introduce a consistent development environment to the new developer.
4. I got a clean host machine - I won't be worried of any patch update from our HelpDesk team :).
5. One reason to get a faster machine ;)

Tips on deploying Silverlight and RIA on the server

http://timheuer.com/blog/archive/2009/12/10/tips-to-deploy-ria-services-troubleshoot.aspx

How-to copy calendar item in SharePoint

ClientContext client = new ClientContext(http://myweb/mysite/calendar);
var web = client.Web;

List listD = web.Lists.GetByTitle("Destination_Calendar");
client.Load(listD);
client.ExecuteQuery();

//clear destination list
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "";
ListItemCollection listDItems = listD.GetItems(camlQuery);
client.Load(listDItems);
client.ExecuteQuery();

//remove list items from the destination
foreach (ListItem li in listDItems.ToList())
{
li.DeleteObject();
client.ExecuteQuery();
}


//get source items
List list = web.Lists.GetByTitle("Source_Calendar");
client.Load(list);
client.ExecuteQuery();
camlQuery = new CamlQuery();
camlQuery.ViewXml = @"";
ListItemCollection listItems = list.GetItems(camlQuery);
client.Load(listItems);
client.ExecuteQuery();

ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();


//add list item to the destination list
foreach (ListItem listItem in listItems)
{

ListItem newItem = listD.AddItem(itemCreateInfo);
newItem["Title"] = listItem["Title"];
newItem["Description"] = listItem["Description"];
newItem["EventDate"] = listItem["EventDate"];
newItem["EndDate"] = listItem["EndDate"];
newItem["Category"] = listItem["Category"];
newItem["ParticipantsPicker"] = listItem["ParticipantsPicker"];
newItem["RecurrenceData"] = listItem["RecurrenceData"];
newItem["fRecurrence"] = listItem["fRecurrence"];
newItem["EventType"] = listItem["EventType"];
newItem["XMLTZone"] = listItem["XMLTZone"];
newItem["UID"] = System.Guid.NewGuid();

newItem.Update();
client.ExecuteQuery();

}