SQL Server 2005 Service Broker

So we've started doing some enterprise application integration and it was decided that we will use the Service Broker platform built into SQL Server 2005. Here is a good primer on the subject. Microsoft has continued with Service Broker in SQL Server 2008 as well.

Locating Special Characters in T-SQL

I ran into a situation recently where we were performing a data load from a SQL Server database to a third-party application and the load failed because of CrLf (carriage-return + line-feed) and Lf characters in large string field. The solution was to strip out the characters (and in this case replace them with HTML markup). Here's what I used to find the characters in the source database...

SELECT COUNT(*)
FROM
[TableName]
WHERE
[FieldName] LIKE '%' + char(13) + char(10) + '%'

Char(13) is a carriage-return and char(10) is a line-feed.

To swap the characters for HTML tags I just used the SQL Server Replace function...

REPLACE(@CharacterString, char(13) + char(10),'<BR/>')

Displaying the List of Column Names in SQL Server

Two ways to display the list of column names for a table. The examples use the AdventureWorks database.

1. Use the sp_help procedure.
   

   exec sp_help 'HumanResources.Employee'

2. Query the information_schema table.

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'HumanResources.Employee'
ORDER BY ordinal_position

10 Myths about Charles Darwin

I greatly enjoy reading and thinking about evolution. Here's an interesting article about Charles Darwin exposing some common fallacies (some of them I thought were true). To date I've only finished reading the Voyage of the Beagle but in my experience you get a much greater understanding of things when you go to the source. If you want a real eye-opening experience (besides reading the works of Darwin or Ernst Mayr of course) check out the TalkOrigins archive.

How to identify your SQL Server version and edition

Here's all you need (Article ID 321185)...

http://support.microsoft.com/kb/321185

NLB vs. Hardware

Joel Oleson has a good article about Microsoft's Network Load Balancing (software-based), hardware-based load balancing, and some good pointers for when not to use NLB.

Vista x64 Error: Profile Service Failed Logon

Logging onto my home network I received the following error this week:

The User Profile Service service failed the logon.
User profile cannot be loaded.

I found the following two solutions online but chose the second because my profile contained a great deal of information (desktop icons, etc.) that I didn't want lost (I do use Windows Live OneCare but I didn't trust that I had current backup)...

1. Delete profile and restore from backup (this is the official Microsoft solution - Article ID: 947215).
   
   
2. In the Windows Registry locate the key for your profile (under HKLM\Software\Microsoft\Windows NT\CurrentUser\ProfileList\), delete the one with the .bak extension, and rename the Temp profile.

Find All SQL Server Stored Procedures Containing Table Reference

This a real quick and dirty approach for locating all procedures in a SQL Server (2000/2005/2008?) database that contain a reference to a table name, or any other database object or string for that matter.

use [db name]
select distinct o.name
from syscomments c
inner join sysobjects o on o.id = c.id
where o.type = 'p'
and c.text like '% [name of db object or string] %'
--and o.name not like 'rpt_%'
order by o.name

In the 'where' clause above the sysobjects.type field is limited to 'p' for [stored] procedure. Another common type I have been searching in lately is a function 'fn'. Someone compiled a helpful list of sysobject types, comparing SQL Server 2000, 2005, and 2008 here.

SharePoint Designer Now Free?

Looks like SharePoint Designer will be free starting April 1, 2009. I was unable to verify this from an official Microsoft site but did see it referenced on more than website.

Update (4/8/2009): SharePoint Designer 2007 can be downloaded free of charge from the official MS Office SharePoint Designer site.
.

Two Approaches for Deploying SharePoint Solutions

I've come across two approaches for deploying SharePoint Solution Packages. There are of course other ways of doing this, including a cafeteria-style combination of both of these. Also, I haven't covered deploying Features as Solution Packages using Visual Studio 2005 here.

To give some background and perspective the following figure depicts the three steps in the SharePoint Solution deployment process...

1. First the Solution is added to the Solution Store.
   
2. Next the Solution is deployed from the Solution Store to the Content Sites (these can be Site Collections or Sites).
   
3. Finally, if it was deployed as a Feature (a recommended Best Practice) the Solution must be activated at the Site Collection or Site it was deployed to.

Below are the two approaches I've seen and used. I've only used the GUI approach in Visual Studio 2008…

• Command line. This approach can be used universally (regardless of whether the Solution was written in VS 2005 or 2008). However it is manual and generally more prone to user error. You also cannot create a script to complete this process using GUI tools the way you can using the command line. Some samples of stsadm.exe usage are here.
  
  • Add solution: From a command prompt navigate to the directory location of stsadm.exe (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\) and enter the following command. If you are not deploying a Feature you can use the file path and file name of the .dll instead. TechNet reference.
    

    stsadm –o addsolution –filename [Path & filename of wsp]

  • Deploy solution: From the same file path use the following to deploy the Feature to the Farm. TechNet reference.
    
    
    

    stsadm –o deploysolution –name [filename of wsp] -immediate

  • Activate solution: This is only necessary if you are deploying a Feature. If you are deploying a .dll you should not perform this step. TechNet reference.
    
    
    

    stsadm –o activatefeature –name [name of Feature Directory]
    

• GUI. This approach uses a combination a combination of Visual Studio 2008, SharePoint CA (Central Administration) and a SharePoint Content Site. As a prerequisite I am assuming you have VSeWSS 1.2 installed (Scot Hillier has an interesting in-depth review of VSeWSS 1.2 here).
  
  
  
  • Add solution: In Visual Studio build the Solution binary by right-clicking the Solution in the Solution Explorer window and selecting Build Solution. If the build is successful repeat the same process but this time select Deploy Solution.
  
  
  • Deploy solution: In SharePoint CA, on the Operations tab under Global Configuration select "Solution management". Locate your Solution (.wsp) in the list of Solutions in the Solution Store and click it. On the Solution Properties page click "Deploy Solution".
  
  
  • Activate solution: If the Solution was not deployed as a Feature this step is unnecessary. If the Solution was deployed as a Feature it will be necessary to activate the solution. At the Site Collection level this can be done by doing the following. Navigate to the Site Collection TLS (top-level site). In the upper right corner select Site Actions > Site Settings > Modify All Site Settings. Under the Site Collection Administration column (to the right) select the "Site collection features" link. On the Site Collection Features page locate your Solution/Feature and click the Activate button.
    

SharePoint Administration Toolkit

The Microsoft SharePoint Product Group has released what looks like a useful SharePoint administration toolkit. The functionality is generally targeted to complex farm or multi-farm scenarios.

SharePoint Error: "The language-neutral solution package was not found"

Every once in a while when I am in the middle of deploying a Visual Studio 2008 SharePoint project (in Solution Explorer, right-click the Solution and select Deploy Solution) I get the following error: "The language-neutral solution package was not found".

So far the only solution to this I've found is to save the Solution, close Visual Studio, then reopen Visual Studio.

Adding Assemblies to the GAC

While working with SharePoint I've found the following approaches (choose any one of the following) for adding assemblies to the GAC. A full explanation for all of the gacutil switches can be found in the Microsoft MSDN Library at http://msdn.microsoft.com/en-us/library/ex0ss12c(VS.80).aspx. Note that these are for a single-server farm scenario running Windows Server 2003 R2.

• If Visual Studio .NET (any version) is installed, Start > All Programs > Microsoft Visual Studio [version] > Visual Studio Tools > Visual Studio [version] Command Prompt. From the command prompt navigate to the folder where the assembly to be added to the GAC resides and enter the following...

gacutil.exe -if "[assembly name]"

• From a raw Command Prompt navigate to the location of the gacutil.exe program (C:\Program Files\Microsoft SDKs\Silverlight\v2.0\Libraries\Server\) and enter the following...

gacutil.exe -if "[assembly file path & name]"

• Log onto the SharePoint server as an administrator and using Windows Explorer copy the assembly to be added to the GAC into C:\WINDOWS\assembly. Do this by dragging the assembly from the bin folder of your VS project into the C:\Windows\assembly folder.

Okay so where's the 12 Hive again?

Typically C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\.
.

Intranet Maturity Framework

Avenue A Razorfish (a Microsoft company) has an intriguing Intranet Maturity Framework Wiki that describes various intranet states based on the relationship between business value and information usage. There are 6 stages identified. On the low end of the scale are intranets that are primarily information publishing in nature. On the upper, more sophisticated end are those intranets referred to as the "Consolidated Workplace Interface" - a unified portal through which everyone in the host organization performs their work or goes for information.

SharePoint Governance

Microsoft has an excellent SharePoint Governance site at http://technet.microsoft.com/en-us/office/sharepointserver/bb507202.aspx.

SharePoint Development with Silverlight 2.0

There are currently three modifications to make to your SharePoint environment for hosting Silverlight 2.0. First install the Silverlight runtime from http://silverlight.net/GetStarted/. Then...

1. Add a MIME type for Silverlight. I'm running my SharePoint farm on Windows Server 2003 (these instructions are a little different for Windows Server 2008). On the SharePoint server open IIS, right-click the server node and select Properties. Click the "MIME Types..." button. Click the "New..." button and add a MIME type. Extension: .xap (include the dot ".") MIME Type: application/x-silverlight-2.
   
   
2. Add System.Web.Silverlight.dll to the GAC. I found System.Web.Silverlight.dll at "C:\Program Files\Microsoft SDKs\Silverlight\v2.0\Libraries\Server\" in my [Windows Server 2003 R2] development environment. So from a command prompt (at "C:\Program Files\Microsoft SDKs\Windows\v6.0a\bin") I entered the command: gacutil -i "C:\Program Files\Microsoft SDKs\Silverlight\v2.0\Libraries\Server\System.Web.Silverlight.dll". Verify the installation by navigating to C:\WINDOWS\assembly (on a Windows 2003 Server OS): System.Web.Silverlight.dll should appear in the list.
   
   
3. Modify the SharePoint web.config file. This file is typically located at C:\Inetpub\wwwroot\wss\VirtualDirectories\80\ but your mileage may vary.

A good article to start with is Light Up SharePoint With Silverlight 2 Web Parts. Be sure to do the above in the order given. For example, step 3 (web.config) relies on System.Web.Silverlight.dll being in the GAC.

OWA Web Parts in SharePoint

I was using Outlook Web Access web parts on my "My Site" page and noticed that the "My Inbox" web part doesn't have a "New" button allowing me to create a new email. This seems a bit inconsistent because I can open an email and reply to it.

So I went looking for a way to display the "New" button. I discovered that there is a lot of flexibilty when displaying OWA parts in SharePoint if you are using Exchange 2007. However at my company we are currently using Exchange 2003.

I opened OWA in a browser window and was able to get just my inbox to appear using the http://[server]/exchange/[myname@mycompany.com]/inbox/?cmd=contents. I can do the same with my calendar using http://[server]/exchange/[myname@mycompany.com]/calendar/?cmd=contents&view=monthly. However when I return to SharePoint and attempt to do the same thing I have issues. Site Actions > Edit Page, then Edit > Modify Shared Web Part gets me to the field where I can enter the URL (replace the Mail Server Address"http://[server]/exchange/" with whatever you would like to see the results).

After trying a number of URL/querystring combinations, including those above, I've come to the conclusion that the web part code is obviously doing something to the querystring other than just appending information to it. For example I attempted an injection approach by using one of the URLs above with an ampersand "&" at the end but that didn't work.

I discovered that I could get the entire OWA interface (Inbox, Calendar, Tasks, etc.) in a single My Inbox web part by entering "http://[server]/exchange/?/" (note the question mark denoting the beginning of a querystring) in the Mail Server Address box. This has the benefit of squeezing everything in a single web part rather than spreading several web parts around.

.

MSDN Mag: 10 Best Practices for Building SharePoint Solutions

The March 2009 issue of MSDN Magazine has an excellent "best practices" article for developing in SharePoint.

Outline for SharePoint 2007 Development Environment

With less than a year of SharePoint experience, below is a sketch of the steps I've been taking to build a SharePoint developer desktop environment. I'm sure I will be fine-tuning this. For example, I am not using Disk Differencing which would probably make things easier by using the same base OS image to start...

1. Create a virtual machine (I use MS Virtual PC 2007).
2. Install Windows Server 2003 R2 Enterprise. I'm not certain what the optimal version of Windows 2003 is so a just use the sledgehammer approach).
3. Install IIS (include SMTP).
4. Apply OS patches.
5. Install Visual Studio 2008 Professional.
6. Apply any patches and/or Service Packs.

By installing as a standalone no network accounts are required.

I would also like to install SQL Server 2005 in there somewhere (my guess is this will have to be before SharePoint is installed) but I'm not certain of a few configuration elements yet.

Tony Zink has a number of posts that describe at length how to create a VPC development and demo image at http://www.pptspaces.com/sharepointreporterblog/lists/posts/post.aspx?ID=28. Someone also posted a comment about a VHD Resizer tool that looked handy.

Update (4/8/2009): My wife also has a thorough guide for installing SharePoint on a VPC here.

SharePoint Installation

1. Create the following Domain accounts. Remember to give them strong passwords.
   
   
   1. SP2007Admin
      
   2. SP2007Search
      
   3. SP2007ContentAccess
      
   4. SP2007SSP
      
   5. SP2007MOSSAppPool
      
      
2. Install Base Farm with CA
   
   
   1. Create the VM and installed Windows 2003 R2
      
   2. Install OS patches
      
   3. Install IIS
      
   4. Install patches
      
   5. Install .NET 2.0 and 3.0 frameworks
      
   6. Install patches
      
   7. Install SQL Server 2005
      
   8. Install latest Service Pack and patches/hotfixes
      
   9. In SQL Server add the SP2007Admin account created above to SQL Server…
      
      1. Grant the account login access (Security > Logins) to the SQL Server
         
      2. Add the account to the following SQL Server Roles (Security > Server Roles): sysadmin, securityadmin, dbcreator
         
   10. Log off the VM and log back on using the SP2007Admin account.
       
   11. Install SharePoint. This will create the CA (Central Administration) site. By convention I use 5555 as the port for CA.
       
   12. Add your account to the Farm Administrators group (Site Actions > Site Settings > Users & Permissions > People & Groups > New > Add Users)
       
   13. Add services (Operations > Topology & Services > Services on Server).
       
       1. Turn on WSS Search (use [domain]\SP2007Search & [domain]\SP2007ContentAccess accounts)
          
       2. Next turn on MOSS Search (use SP2007Search)
          
       3. Start Excel Services (only available with SP 2007 Enterprise version)
          
          
3. Create SSP
   
   **Prerequisite: Search must be turned on and configured.
   
   
   1. Application Management > Office SharePoint Services Shared Services
      
   2. New SSP
      
   3. Create new web app (same screen). By convention I use 6666 as the port for the SSP. Use [domain]\SP2007SSP as the service account.
      
   4. Create a new web app for MySites. By convention I use 7777 as the port for MySites. Use [domain]\SP2007SSP as the service account.
      
   5. After the SSP has been created login to the SSP (SSP1) using the SP2007Admin account. Under Site Actions > Site Settings > Site Collection Administrator (under Users & Permissions) add your network login ID as a Site Collection Administrator.
      
   6. Still in the SSP, Site Actions > Site Settings > People & Groups, click Site Permissions from the Quick Launch bar and add your account with Full Control rights.
      
   7. Still on the SSP Home Page, under User Profiles & MySites, click Personalization Services & Permissions. Add your network login ID and select all rights.
      
   8. Test your work. Use your own network login ID.
      
      1. User Profiles and MySites: User Profiles & Properties. Start a full AD import. (Note the time and day of the week so you can do this before audience compilation.)
         
      2. Audiences: Create and compile a test audience.
         
      3. Search Settings: not much to test yet…
         
      4. Email settings…
         
         
4. Create a Web Application for Content Sites
   
   
   1. In CA > Application Management (tab) > Create or Extend Web Application > Create New
      
   2. Use port 80
      
   3. Use the [domain]\SP2007MOSSAppPool AD account
      
   4. Create the web application and navigate to Create Site Collection via link.
      
   5. Create Site Collection using the Publishing (tab) > Collaboration Portal template.

Nice California Red (Folie A Deux)

I had a red wine with dinner last night that I thoroughly enjoyed: Folie A Deux - Menage A Trois.

Tips for Analyzing SharePoint Usage and Performance Diagnostics

Two recent posts on the SharePoint Team Blog I would like to examine more thoroughly when I have the chance...

1. Analyzing Microsoft SharePoint Products and Technologies Usage.
   
   
2. SharePoint Diagnostics (SPDiag) Tool v1.0 for SharePoint Products and Technologies.

STSADM

The following sample stsadm commands are useful when working with Features and Solutions. The stsadm.exe application can be found at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\.

Deactivate a feature:
stsadm –o deactivatefeature –name wileyfeature–url http://sp2007dev/sitedirectory/wroxprosp2007teamsite

Uninstall a feature:
stsadm –o uninstallfeature –name wileyfeature

Add a solution to the Solution Store:
stsadm –o addsolution –filename c:\wsp\wileyfeature.wsp

Deploy a solution from the Solution Store:
stsadm –o deploysolution –name wileyfeature.wsp -immediate

Retract a solution from the Solution Store (do this before deleting the solution completely from the Solution Store – this is the opposite of the deploysolution switch):
stsadm –o retractsolution –name wileyfeature.wsp –immediate

Delete a solution from the Solution Store (do this only after retracting the solution from the web server – this is the opposite of the addsolution switch):
stsadm –o deletesolution –name wileyfeature.wsp

-----