Ideas & Solutions Etc.

Validate EDI Instance Data

I was trying to validate an instance of X12 4010 867 file in VS.NET 2005 IDE and could not get it work correctly using the schema shipped with BizTalk Server 2006 R2. After several tries and struggles, Here is what I did to make it work:

1. You need to remove the ISA/GS ... GE/IEA from the instance document.

2. If the Segment teminator is ^, which is the default for Repetition Separator then you need to fake out an repetition separator which is not inside your edi file and then specify the ^ as your segment terminator.

URL That Will Open InfoPath Form in Browser

If you ever need to send out a link of InfoPath form in email and want the link to open the form in browser instead of popup the xml in open/save dialog ... here is the trick:

send the link as following:

siteUrl/_layouts/FormServer.aspx?xmlLocation=InfoPathFormUrl?DefaultItemOpen=1

make sure the InfoPath form Url is UrlEncoded.

Setting BDC Permissions

You need to set BDC permission at application level AND also entity level otherwise you would spend lots of time figuring out why you got error like 'You do not have permission to access ...' or 'Unable to connect to ... '

Move WSS3.0/MOSS2007 Databases To Different Server

Recently I had to move all databases including the content database in a MOSS 2007 farm from one SQL server to another. I tried few methods and found the following steps are the easiest:

[Assumption: All databases in the farm are on one SQL server and the SQL server was not assigned to any other roles]

1. Quiesce the farm ... make sure no one is accessing the farm
2. List all databases in the farm (http://blogs.msdn.com/johnlee/archive/2008/03/09/list-all-databases-in-a-moss-2007-farm.aspx)
3. Detach all databases from the above list
4. Copy all databases to another SQL server and attach all of them
5. Go to one of Web Front End (WFE) server
   • Launch regedit and navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB, double click the dsn and change the Data Source from the original SQL Server name to new SQL Server name
   • Run stsadm.exe -o renameserver -oldservername <OldSQLServerName> -newservername <NewSQLServerName>
6. Repeat step 5 on all WFE and other servers except the SQL server
7. Reboot all servers except the SQL server

[Assumption: WSS/MOSS was installed on a single box - the new SQL server name will be "NewSQL"]

1. Introduce the new SQL server ("NewSQL") into this farm by creating a web application pointed to the new SQL Server
2. Quiesce the farm ... make sure no one is accessing the farm
3. List all databases in the farm (http://blogs.msdn.com/johnlee/archive/2008/03/09/list-all-databases-in-a-moss-2007-farm.aspx)
4. Detach all databases from the above list
5. Copy all databases to a NewSQL server and attach all of them
6. Open SSMS, pointed to configuration database, run the following script to get the Id for the "NewSQL" and record the ID, let's refer it as "InstanceID_GUID"

select ID from objects 
where parentid = (
    select Id from objects 
    where name = 'NewSQL')

7. Since all roles are assigned to single server so I could not use stsadm.exe renameserver command to achieve the same goal. What I did is to run the following script to update the parentID of all databases to above instance Guid

UPDATE objects 
SET parentid = 'InstanceID_GUID' 
WHERE id IN 
(
    SELECT o.id 
    FROM objects o
        INNER JOIN classes c on c.id = o.classid     
    WHERE c.Fullname LIKE '%Administration.SPConfigurationDatabase%'
    OR c.Fullname LIKE '%Administration.SPContentDatabase%'
)

8. Go to one of Web Front End (WFE) server
• Launch regedit and navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB, double click the dsn and change the Data Source from the original SQL Server name to new SQL Server name
9. Repeat step 9 on all WFE and other servers except the SQL server
10. Reboot all servers except the SQL server
11. Change the "Default database server" from Central Administration to "NewSQL"

[Disclaimer] The methods stated above are just how I did and provided as is for informational purpose only and I have not tested them in production environment and do not know if they are supported methods.

List All Databases In A MOSS 2007 Farm

Run the following script in the content database:

SELECT o.[Name] AS 'DatabaseName',
    Instance.[Name] AS 'DatabaseInstance',
    [Server].[Name] AS 'DatabaseServer'
FROM Objects AS o
    INNER JOIN classes c on c.id = o.classid     
    LEFT JOIN Objects AS Instance ON o.ParentId = Instance.Id
    LEFT JOIN [Objects] AS [Server] ON Instance.ParentId = [Server].Id
WHERE c.Fullname LIKE '%Administration.SPConfigurationDatabase%'
    OR c.Fullname LIKE '%Administration.SPContentDatabase%'

Global Tax Refund

This is something off topic ... some experience in Italy.

When you purchase certain amount of item in European countries you are entitled to get the 12% VAT tax refund when you leave the last European country ... sounds terrific idea, right?

In reality, it's very hard to get it because you can only do it at the airport where you are leaving Europe, you need to get the receipt stamped at the custom with your original item checked, you might need to find out where is the refund counter and most importantly, you might not have enough time between your international flights ...

My wife and I enjoyed our Christmas vacation in Venice and Rome and we did buy some items ... we could not get the tax refund a Rome airport and we can only get the receipt stamped in Frankfurt airport ... our flight delayed so we have barely 1.5 hours, when we got there we asked where the tax refund office was and we got different answers from different people so we just walked around with all of baggage ... from terminal A to terminal B and finally found the custom office for tax refund ... I saw one lady came out of the custom office in tears and I asked her what happened, she told me that it took her more than one hour to find this custom office and she almost gave up the tax refund!!!

We managed to get our refund and get on our airplane back SFO ... It's been a while I travel outside US and when I got home I felt I do not have any complaint anymore, home sweet home and US is the best country to live in.

BizTalk MQSC Adapter

Previously, server based BizTalk adapter was used to access MQ Series and that requires MQ series server on Windows as intermediate server between BizTalk Server and non-Windows Queue Managers. In some situation, when installing the MQAgent to Windows MQ Series box is not an option the client based BizTalk adapter (MQSC) is needed to achieve the same goal. 

MQSC is not part of BizTalk bits but it can be found in Host Integration Server 2006.

While setting up MQSC adapter, here are some of the tips when authorization issues occurred (Thanks Tom Canter on this):

1. If MQSeries server box and BizTalk Server box are in the same domain

• Configure the MQSC receive/send adapter using a host instance with domain account as logon account
• Add this domain account to domain security group: "<YourDomainName>\Domain MQM"

2. If MQSeries server box and BizTalk Server box are NOT member server of any domain

• Create a username on both BizTalk Server box and MQ Series server box with identical username and password
• Add the above created account into local group "mqm" on BizTalk server and MQSeries server
• Configure the MQSC receive/send adapter using a host instance with the above created username as logon account

San Diego Fires

This is the first time I experience this type mandatory evacuation ... around 6AM this morning, reverse 911 call notified us to evacuate immediately.

Sitting in a corp. office at High Bluff Drive offered by my former boss, listening to the AM 600, surfing the net to check the fire conditions ... air condition is pretty bad and we can smell the smoke inside the office.

There is 50% chance that my home was burned but we are safe, my dog is with us and our two birds are with us too ... but I felt so sorry that I left my 4 fishes at home.

{"value does not fall within the expected range."} error

When I use the object model to add item to MOSS 2007 project task list, I have some code like following:

const string SitePath = "http://litwareserver:5000";
const string ListName = "ProjectTask";

// enter object model through site collection.
SPSite siteCollection = new SPSite(SitePath);

// obtain reference to top-level site.
SPWeb site = siteCollection.RootWeb;
site.AllowUnsafeUpdates = true;     //needed since my code will be running as web service

SPList myList = site.Lists[ListName];
if (myList != null)
{
    SPListItem item = myList.Items.Add();

    item["Title"] = "test item";
    item["PercentageComplete"] = 0.2; // 20% 

    item.Update();
}

// clean up by calling Dispose.
site.Dispose();
siteCollection.RootWeb.Dispose();
siteCollection.Dispose();

I got the "value does not fall within the expected range." error

By looking at this error message, my first reaction was to check the percentage complete column, it has min (0) and max (1) defined, 0.2 shall be within the range, anyway, I tried 20, 0, 1 and lots of other values, same error;

Then I wanted to reproduce it with a custom list so I created a new list with a field with min/max values defined, I could add items with values inside the range and also outside the range.

I could not reproduce it with custom list. What could be the issue? What's the difference between my custom list and the project task list? Is it possible the field (% complete) in project task list was defined in the content type?

Then I created a content type that has a field with min/max values defined and attached this content type to a new list, removed the original item content type from the list, tested it again, I could add item to the new list with values inside or outside the defined range. So it's not because it's defined in content type ... this made me wonder what could be the problem???

I decided to print out the internal name and other properties of each fields and found out the internal name of field (% complete) is "percentComplete" instead of "percentageComplete" - I fixed the problem right away.

What I learned from this debugging process?

1. Do not assume anything.

2. Do not be fooled by error message.

3. Product error message should contain meaningful and specific information to help identifying the problem.

The error message should be the classic "index is out of range" or even better "Cannot find the field name '{0}'" 

WaitHandle.WaitAll For Multiple Handles on a STA Thread Is Not Supported

While developing WinForm application, if you need to spawn multiple threads to work on different tasks and you need to wait all threads to complete ... then you might think to use the following approach:

>>> Create an array of AutoResetEvent; use ThreadPool.QueueUserWorkerItem to spawn each task in different thread; then call WaitHandle.WaitAll(AutoResetEvent[]) to wait all threads to complete the task

WinForm app is attributed as [STAThread] and you will get "WaitAll For Multiple Handles on a STA Thread Is Not Supported" error ... what's the solution? Here is what I used for this scenario:

>>> Create a ManualResetEvent; maintain a TaskCounter; use ThreadPool.QueueUserWorkerItem to spawn each task in different thread; Call ManualResetEvent.WaitOne(); Decrement the TaskCounter after each task is completed and Call ManualResetEvent.Set() when the TaskCounter == 0

Special notes: In your DoTask(object stateInfo), you need to handle exception properly and make sure the TaskCounter is subtracted should there is an exception 

Workaround to Search Results RSS URL Shows Internal Links

Symptom:

If you have a public facing site built on MOSS 2007 you normally would have two zones defined: default zone that is your local intranet and Internet zone that's your public facing zone. In Alternate Access Mapping, you would put something like http://servername in the default zone and put http://www.publicdomainname.com in your Internet zone; then you would create a content source to crawl your site as http://servername to if you want all documents stored in list to be crawled. This works very well but there is one area that the internal URL will be shown:

Enter something in the search box, click search, on the search result page, if you have RSS turned on, you would see an RSS link to allow you to subscribe the search result feed. Click on the RSS link icon, you would see all URLs are shown as internal http://servername  

This is a bug - because the specific WebPart always get URL from default zone. 

Workaround:

Go to Alternate Access Mapping, public the public facing URL into the default zone and put your local intranet URL into custom zone.

Tips and Tricks From MOSS 2007 Development Training

• General
• do not change identity account of application pool that is used by WSS v3 from ISS mgr, change it from WSS admin site
• if something needs to be accessible to all sites, deploy to _layout\[yourCompanyName] folder
• get CAML IntelliSense by create a file named as sharepoint_catalog.xml contains the following

<SchemaCatalog xmlns="http://schemas.microsoft.com/xsd/catalog">
  <Schema href="C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\wss.xsd" targetNamespace="http://schemas.microsoft.com/sharepoint/" />
  </SchemaCatalog>

and copy this file to the following folder C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas\

• do not change feature GUID after it's deployed and activated
• turn on auditing only if absolutely necessary
• do not use space with your list column names
• use content field control instead of content edit WebPart for publishing
• load balance your central admin site too
• avoid custom site definition if possible - use features
• use features and solutions to deploy WSS artifacts if possible
• use MetaTagsGenerator to generate META tag for SEO (search engine optimization) 
• Application Pages Customization
• use theme if possible and meet your needs
• do not modify the original _layout folder; make a copy of _layout folder and change your web application point to the _[your]layout folder
• WebPart Development
• unless you intend to use your WebPart on SharePoint 2003 site inherit your WebPart class from System.Web.UI.WebControls.WebParts.WebPart
• deploy WebPart dll to bin folder instead of GAC [full trust] if possible
• remember to add [assembly: AllowPartiallyTrustedCallers()] to allow the strong named assembly to run from the bin
• remember to change trust level in web.config from "WSS_Minimal" to "WSS_Medium" or create your own policy if your WebPart uses Object Model
• Using Object Model
• Dispose the SPWeb and SPSite objects as soon as they are no longer needed

class Program
{
    static void Main()
    {
        string sitePath = "http://litwareinc.com";
        // enter object model through site collection.
        SPSite siteCollection = new SPSite(sitePath);
        // obtain reference to top-level site.
        SPWeb site = siteCollection.RootWeb;

        SPWeb site1 = site; //this actually create a copy of site, I will have followup blog on this after I find out how the assignment of an reference type got a copy instead of a pointer 
        site1.Dispose();   //this does not affect site          

        // enumerate through lists of site
        foreach (SPList list in site.Lists)
        {
            Console.WriteLine(list.Title);
        }
        // clean up by calling Dispose.
        site.Dispose();
        siteCollection.RootWeb.Dispose();
        siteCollection.Dispose();
    }
}

• Some MOSS resource links
• http://www.andrewconnell.com/blog/  [instructor's blog]
• Microsoft Cabinet SDK
• U2U's CAML Builder 2007
• Application Pool Recycler
• Stramit's SharePoint CAML 2007 Viewer 
• Feature Explorer
• SharePoint Manager 2007
• MOSS Query Tool
• How to extend STSADM.exe?