I recently was faced with a situation that needed to update a field in a table with a NUMERIC RANK based on values in certain column(s).

Traditionally we would have written scripts that were either PL\SQL to sort data etc. using cursors etc. and then update the field or write a program (in case of huge datasets) that sorted the data in a temporary dataset and then write update statements. Well….I did not want to do that, I wanted to write something short and sweet that could be reused and could scale in large datasets.

 

I started thinking about using a combination of the newer SQL Server features COMMON TABLE EXPRESSION, RANK( ) and UPDATE sql. I rank into a couple of roadblocks initially e.g. when I tried writing a common table expression in a scalar function – it did not allow that. In the end, I did get it working.  I have also attached a demo SQL script use_cte_rank_toupdate.sql (1.4 KB) that is a self reliant script that demo's the idea.

 

VISUALISATION

 

Here are the steps – you can essentially replace each step with specific SQL’s that correspond to your business needs – but the high level steps\concepts can be followed.

 

STEP 1: Write a SQL Statement that first sorts your data using the RANK() function

STEP 2: Wrap the SQL Statement from Step 1 in a COMMON TABLE EXPRESSION WITH syntax

STEP 3: Write an UPDATE statement to update the original TABLE  that joins the COMMON TABLE EXPRESSION with the ORIGINAL TABLE to update the NUMERIC RANK column

 

Essentially the idea is to use these new features to make development simpler - I hope this article spawns different ideas in your minds of leveraging your SQL features.

Using Excel to create an intermediate XML file to help export SharePoint list data to SQL Server

When adding a web part to a Web Page in DESIGN MODE OR implicitly RUNNING a web part when you load a page,you might face issues like the page not loading due to recent code changes in the web part code.

 

Here are some basic steps to troubleshoot and Debug the code in your SharePoint web part.

 

** IMPORTANT NOTE: The steps should ONLY be really used in your DEVELOPMENT ENVIRONMENT. Once you are done with your debugging\troubleshooting – please REVERT your edits. If these changes get into your production site they could cause security\performance issues. To make it simple make a copy of your web.config before you start.

 

Viewing the CALL STACK on your web page – Open your SharePoint Web Site’s web.config

1. Find the tag <SafeMode MaxControls="200" CallStack="false". Change the CallStack value to true
2. Find the tag <customErrors, set the mode=”RemoteOnly”
3. Run your web page

DEBUGGING CODE in your Web Part using Visual Studio 2005\2008

1. Set the debug flag to true in web.config < compilation batch="false" debug="true">
2. Open your Web Part Code Solution in Visual Studio
   • Go to Project properties, in the Debug tab make sure your “Start browser with URL” points to your SharePoint site where your web part is hosted
   • Make sure your break points are set. Click F5.

Notepad++ is an excellent tool if you are a developer that wants to have free form text editing in one spot. Of course it does not give you intellisense (Microsoft TM) but it gives you the formatting etc. to view and make minor changes. E.g. you can use it to edit SQL files or VB files all in one spot. Defintely a must have for all developers.

URL http://notepad-plus.sourceforge.net/uk/site.htm

If you create a web part using Visual Studio 2008 extensions for Windows SharePoint Services 3.0, v1.3 you may not be able to Deploy your Web Part to your SharePoint web site (WSS\MOSS).  Your first step off course should be to read the release notes posted by Microsoft as suggested by the error message. In my case the issue turned out to the security account that was linked to the VSeWSS WCF Service did not have administrator permissions.

 

Steps that caused the error

1.       Create new web part project and solution, choose deployment to bin directory.

2.       Choose the Deploy option by right clicking on the solution in the Solution Explorer.

 

Solution that worked for me à Added the ‘Network Service’ account to the Administrators group.

 

Finding Root Cause and Solving it (if it was similar to mine)

1.       Open IIS and navigate to the Web Sites folder. Select VSeWSS.

2.       Open the properties window for VSeWSS. Note the Application Pool value under the Home Directory tab.

3.       Under IIS go to the Application Pools folder, locate the Application Pool for VSeWSS.

4.       Open the properties window for the Application Pool. Note the Security Account value under the Identity tab.

5.       Open Computer Management. Locate the Administrators Group under Local Users and Groups.

6.       Make sure the Security Account is added to the Administrators group.

·          If the security account is not under the Administrators group your root cause should be similar to mine.

·          Helpful Note: If you are working on a Virtual\Local Server you may not be able to find your security account e.g. you may not be able to find your Network Service account, make sure you are searching for the account in the right spot by choosing the right Location (Network or local machine) when the Add Users… dialog comes up.

7.       Restart IIS if you had to add the security account and try deploying your web part again.

 

My Error Description (When trying to Deploy the web part)

“Error       1              VSeWSS Service Error: Assembly C:\Documents and Settings\username\My Documents\Visual Studio 2008\Projects\MyWebPartsOne\bin\Debug\MyWebParts.dll not found. This may occur because the VSeWSS WCF Service does not have local administrator permissions. Please review the release notes.

VSeWSS Service Logging Error: Access to the path 'C:\Documents and Settings\Default User\Application Data\Microsoft\VSeWSS 1.3' is denied.

Logging failed attempting to write to C:\Documents and Settings\Default User\Application Data\Microsoft\VSeWSS 1.3\VSeWSS1.3 service.log. This may occur because the VSeWSS WCF Service does not have local administrator permissions. Please review the release notes. “

 

URL for Visual Studio 2008 extensions for Windows SharePoint Services 3.0, v1.3

http://www.microsoft.com/downloads/details.aspx?FamilyID=B2C0B628-5CAB-48C1-8CAE-C34C1CCBDC0A&displaylang=en

The ‘IE (Internet Explorer) Developer Toolbar’ is a tool that allows web developers and designers to inspect elements on a web page i.e. the DOM model. For example, if you are a SharePoint designer\developer who wants to create\edit a theme, you can use this tool to click on screen areas\elements to figure out what image files in the theme actually correspond to screen areas\elements. The toolset shows you HTML object class names, id’s, link paths and a host of other information as well. This toolset is also great for debuggin stylesheet problems. Read more about the toolset at the URL below.

 

To download this toolset go to http://www.microsoft.com/downloads/details.aspx?familyid=e59c3964-672d-4511-bb3e-2d5e1db91038&displaylang=en

 

Quick Start à Download and Install Toolset à Open a new instance of internet Explorer à Open a SharePoint site à From the top level IE menu Choose View->Explorer Bar -> IE Developer Toolbar. You should not see a high level summary of your site in the IE toolbar pane that is open at the bottom. In the pane below choose Find-> Select Element by Click, this is an easy way to point + click and identify individual HTML elements.

If you are using Mozilla's FireFox a better toolset is the Firebug available at http://getfirebug.com/

 

I have used events in the past extensively when developing windows applications. I just discovered a new feature recently when developing a windows workflow application. The ability to execute Code when Host Programs Add\Remove\Raise Events in your custom control\object.

I have developed a sample project to demonstrate the custom event feature. When you run the solution click on the "Add Handler" and "Remove Handler" buttons, you will see custom code getting invoked. One needs to be familiar with the base windows event\delegate model before your understand what is happening here to appreciate it fully. 

Download CustomEvents.zip, unzip it and run the solution.

CustomEvents.zip (36.98 KB)

Team Foundation Server, setting up your version control folders and branch relationships to properly merge code in the model

There is a code camp  at Reston, you will find more information at http://novacodecamp.org/. Code camp is a ONE day deal where you can sit and listen to industry technical guru's belt out presentations on the latest topics in the Microsft Technical Arena and best of all it is free. A must go...

If your .NET solution contains projects that contain custom/user controls for windows/web forms these controls are automatically loaded into the toolbox so that they can be utilized on your forms. In some cases when there are a large number of controls in your project the solution seems to compile slowly or the toolbox seems to take quite some time to load. If you are facing such issues turn off the AutoToolboxPopulate property on the ToolsàOptionsàWindows Forms DesigneràGeneral tab.

Something basic, Not that you would but you could...If you want to debug .NET solutions without Visual Studio you can use DBGCLR.EXE to do so which gives you a GUI interface to debug .NET Executables/Programs/Processes. To use this you either need to have the .NET SDK 1.1/2.0 installed or Visual Studio (in which case you don't need to use DBGCLR). Users who have SDK installed can find the tool at Microsoft.NET\FrameworkSDK\GuiDebug. Users who have Visual Studio installed can find this tool at Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\GuiDebug for .NET 1.1 or Program Files\Microsoft Visual Studio 8\SDK\v2.0\GuiDebug for .NET 2.0.

 

Click here  http://msdn2.microsoft.com/en-us/library/d9kh6s92.aspx to get a listing of tools for the .NET Framework.

I have uploaded the Mirosoft Reston Code Camp presentation and source code for my talk on custom controls (with data binding) at http://www.knowthycode.com/Downloads/20060608/WinForms_Custom_Controls_Presentation_Clyde.zip

The schedule and details for the Reston Code Camp can be viewed at http://www.madcodecamp.com/schedule/codecampmain.htm. Hope to see you there.

Microsoft will be hosting a Code Camp on June 10’Th. The code camp is a great event for .NET developers to network and also gain a lot of information on the topics that are current and unique. The speakers in Code Camp are experts on topics they speak on and have a lot of experience in the subject they are presenting; it is a good opportunity for developers to get questions answered in person. You actually see code being developed on the fly!!!

Call for Speakers- To present on a wide variety of topics from Web Applications, Smart Clients, Data Access and Security Best Practices.

Whether you are a speaker or an attendee we would love to have you participate in this developer event.

Whether you are a speaker or an attendee find out more here.

Boosting performance when using solutions that contain large Visual Basic projects in Visual Studio 2005